Cream of the Crop 22

home *** CD-ROM | disk | FTP | other *** search

/ Cream of the Crop 22 / Cream of the Crop 22.iso / database / eb108.zip / PACK1.PRG / EBOUT / EB3.REP < prev next >

Wrap

Text File | 1996-11-10 | 202KB | 8,865 lines

════════════════════════════════════════════════════════════════ EASY BASE PROGRAMMERS REFERENCE Contents ──────────────────────────────────────────────────────────────── Procedure Commands Page ────────────────── Clear Records From 11 Copy All From 14 Create Temp As 15 Declare 27 Delay 30 Delete Record 32 Derive Off 33 Display Status 36 Do..Loop 37 Escape On/Off 39 Exit Batch Menu 40 Exit Procedure 41 For..Next 48 If Then Else 61 Index Off 63 List Variables 73 Lock / Unlock 74 Manual Feed 79 Multiple Lines 86 Next Batch 87 Odd/Even Page Print 88 Pack 97 Pause On/Off 100 Preselect 102 Print 103 Printer Control 104 Recall Input Screen 110 Remark 112 Reset Sequence 113 Run 121 Show Picklist 128 Skip Group 130 Skip Record 131 Subindex 137 Update Record 147 ................................................................................ Functions Page ───────── Abs 1 Chr$ 10 Datetext 23 Dayofmonth 24 Dayofweek 25 Dayofyear 26 Default 28 Hours 59 If 60 Intext 68 Jointext 69 Lefttext 70 Lengthtext 71 Lookup 75 Lower 76 Makedate 77 Maketime 78 Maths 80 Midtext 82 Minutes 83 Mod 84 Month 85 Proper 106 Random 107 Retail 115 Reverse 117 Righttext 118 Round 119 Seconds 123 Spacepad 132 Spellday 133 Spellmonth 134 Stringof 136 Timeampm 144 Upper 148 V.A.T. Functions 149 Year 151 Zeropad 152 Operators Page ───────── Arithmetic 92 Logical 94 Relational 93 ................................................................................ Utilities Page ───────── Alter Vat Rates 2 Backup Application 4 Change Password 8 Data Import 20 Delete Choice Lists 31 Install Form 64 Install Printer 65 Install Procedure 66 List File Names 72 Options 95 Pack Data Forms 98 Rebuild Directories 109 Restore 114 Screen Colours 122 Set Paper Length 126 Field Types Page ─────────── Choice 9 Date 22 Fixed Point 46 Floating Point 47 Formatted Text 54 Integer 67 Text 141 Text Block 142 Time 143 Field Derivation Prefixes Page ───────────────────────── Autodial 3 Current 16 Disable 34 Goto (Field) Next 57 Help 58 Retest 116 Start Here 135 User Menu Functions Page ─────────────────── Menu Calls 81 ................................................................................ System Values Page ───────────── Blank. 6 Bottom Margin 7 Current Record 17 Cur_fld 19 File_date File_time 42 File_len 43 File_pos 44 Fun_key 55 Global Number 56 Output 96 Page Number 99 Pi 101 Prog_dir 105 Record Number 111 Sequence 125 System Date 139 System Time 140 Total Copies 145 Total Records 146 File Commands ───────────── Close 13 Erase 38 Find 45 Open 91 Read 108 Seek 124 Shell 127 Write 150 Field Controls Page ────────────── Beep 5 Clearfields 12 Cursor 18 Delay 29 Disable 35 Run 120 ................................................................................ ABS FUNCTION ABS This function returns the absolute (positive) value of a number. Ex. abs(length - breadth) This returns 6 if length = 12 and breadth = 6 or if length = 6 and breadth = 12 Acceptable parameters Numeric value Numeric expression Numeric field/variable Any function which returns a numeric value - 1 - ................................................................................ ALTER VAT RATES UTILITY ALTER VAT RATES The five VAT rates used by the VAT functions can be altered either from the utilities menu or from a user menu function. Just select "Alter VAT rates" - edit the percentages and press F2 to save the new values. - 2 - ................................................................................ AUTODIAL DERIVATION PREFIX AUTODIAL Easy Base will dial (via a Hayes compatible modem) direct from any form or procedure input screen. To use the Auto dialling facility, all you have to do is place the "autodial" keyword in the derivation of the field which contains the telephone number. If the field already has a derivation then add "autodial" as a prefix. Ex. Autodial Lookup(customers,phone) To initiate the call type Ctrl + D. By default Easy Base uses tone dialling via COM1. If your modem is connected to COM2 you can set this up from the Options item on the Utilities menu. If your exchange does not recognize tone dialling then you can pulse dial by pressing Ctrl + P. If you operate from a private exchange and have to wait for an outside line after dialling 9 then place a "W" between the 9 and the rest of the number. Note:- 1. You can have more than one prefix on any one field derivation provided there is a space between each. 2. The Autodial prefix cannot be tested in derivation test mode. - 3 - ................................................................................ BACKUP APPLICATION UTILITY BACKUP APPLICATION Easy Base includes an inbuilt Backup system for your applications and data. If you select "Backup Application" from the utilities menu then Easy Base will backup your entire application - Choicelist fields - Forms - Data and procedures. The backup system will split large files across multiple disks automatically. When an application is finished and you have backup copies then backing up choice lists and procedures is wasteful of time and floppies. You can backup only the users data by calling "Backup Data" from a user menu. Backup Principles. The reason for a backup is that in the event of a computer or hard disk failure you will have your application or data to restore on the new machine. If you have no backup and your machine fails then you have lost all your data. If you have only one backup and your machine fails while doing a backup then you have lost all your data. The principle of safe backing up is to keep two sets of backup disks in separate boxes marked "Latest" and "Previous". Whenever you do a backup you use the disks from the "Previous" box - move the disks from the "Latest" to the "Previous" and put your new backups in the latest box when completed. This not only overcomes the problem of a failure during backup but also provides a secondary set of disks if any of the "Latest" disks turn out to be faulty when you come to restore. If you ever have to use a backup disk and find that it has developed a bad sector then you can attempt a recovery with a utility such as "Norton" Disk Test. Easy Base leaves approximately 5K of unused disk space on all backup disks so that such a utility has somewhere to move bad sectors to. To save disk space, Easy Base does not backup index files. If you ever have to use a backup disk then the index files are rebuilt after restoring the data. See Also:- Restore - 4 - ................................................................................ BEEP FIELD CONTROL BEEP The "beep" control is used in field derivations to alert the user to input errors. In addition to sounding the beeper the "beep" control has two optional additional parts - A message to be sent to the message line and cursor redirection. This is normally to the field in which the error has been made but can be to any field. Field controls are appended to field derivations and are enclosed in square brackets. Ex. If(partno < 0,blank[beepPart Numbers must not be negativeCursor partno],partno) In this example, if the user enters a negative number in the partno field the computer will beep,the number will be cleared from the field, the message "Part Numbers must not be negative" will be displayed on the message line and the cursor will return to the "partno" field. Ex. If(paid = "no" and chequeno <> blank,paid[beepEither bill has been paid or cheque number entered in errorCursor paid],paid) In Easy Base, division by zero is not trapped as an error. There is a very good reason for this. When you start a new record and all the fields are blank then any field which provides a value for a division in another field would cause a division by zero error before its value had been entered. Whenever Easy Base encounters a division by Zero it divides by one instead. This is perfectly acceptable in most business applications. However, if you need to be warned of a division by zero in formulae then you can provide your own error trap using the "Beep" control. Ex. If( T4 = 0,blank[beepDivision by Zero Value from Field T4], T4) NETWORK VERSIONS From the release of Network V1 there is also a Beep command in the procedure language. See Example under "Recall Input Screen" - 5 - ................................................................................ BLANK. SYSTEM VALUE BLANK. In Easy Base the value of any field which does not contain characters is represented by the word "Blank" irrelevant of data type. A numeric field returns the value "Blank" only when the field is empty. If it has a value of zero then it is NOT blank. In fact, the main advantage of recognizing "Blank" is that you can distinguish between zero value and empty numeric fields. There is, however, one slight disadvantage. If a text field contains ONLY the word blank and you use its value in a derivation then the word "Blank" from the field will disappear. The "Name" field at the top left hand side of this page is derived by the formula Upper(Name). You will notice that "Blank" is followed by a full stop. If it were not then the upper function would return " ". You can also remove a fields contents by setting it equal to blank. - 6 - ................................................................................ BOTTOM MARGIN SYSTEM VALUE BOTTOM MARGIN Throughout a procedure which has a printed output, Easy base keeps track of the length of page remaining (in inches) in the system value "Bottom Margin". The Bottom margin value is used to control pagination in procedures which "Report" on data. The "Bottom Margin" value is based on the paper type entered from the Utilities Menu. You must set the type of paper you are using before using "Bottom Margin". Ex. Line feed: Line feed: Line feed For employees print list items if bottom margin < .5 then page feed: line feed :line feed : line feed else line feed end if next In the above example the list items section is printed with a blank line between each and with a half inch top and bottom page margin. When you base decisions on the "Bottom margin" value you should always do so with a "Greater Than" or "Less Than" operator. If you used " If Bottom margin = .5 then Page feed" your page would only be ejected if and when the "Bottom Margin" value was exactly 0.5 As most printing is done at six lines to the inch it is most unlikely that the "Bottom margin" value would ever be half an inch. - 7 - ................................................................................ CHANGE PASSWORD UTILITY CHANGE PASSWORD You can change your developers access password with this item on the utilities menu. - 8 - ................................................................................ CHOICE FIELD TYPE CHOICE If you define a field as the "Choice" type a window will open into which you can type up to thirty four different choices. When you have entered your choices you will be asked to give a name to your choice list. Once a choice list has been saved it can be used in any other choice field in any other form or input screen. Once a choice list has been saved you can edit the contents but not the field length. Editing a choice list in one form automatically edits it for any other form. If you need to alter a list such that the field length will change then you must create a new list. You can erase the old one from the utilities menu. If the list is used in more than one form then you must redefine it in all the other forms in which it is used When the cursor enters a choice field your choice list will be displayed for the user to choose from. He cannot enter any value other than one from the list. When the cursor enters a choice field that is blank, the list will pop up for the user to choose from. If there are more than nine choices on the list then only the first nine will display automatically. For up to nine choices the user can pick one by pressing the number of the choice or leave the field blank by simply moving the cursor. If there are more than nine choices then the entire list can be displayed by pressing the space bar. When a list of more than nine is displayed the user must select a choice or press the Escape key. When the cursor re enters a choice field in which a choice has already been made the same events occur except that in the case of a two choice list the two choices are toggled on the space bar without actually being displayed. - 9 - ................................................................................ CHR$ FUNCTION CHR$ The Chr$ function returns the character whose ASCII number is supplied as the parameter. Ex. Chr$(171) < Returns "½"> Ex. Jointext(Chr$(227),"r",Chr$(253)) < Returns "πr²" In Easy Base, text fields are automatically justified left. This is desirable in record entry as it prevents the user from entering leading spaces in indexed fields and consequently trashing alphabetic printouts. However, you may wish to right justify or centre justify text in printouts. For example, the righthand "Chr$" at the top of this page is right justified in its field. You can use the ASCII character 255 to achieve right or centre justification. Ex. Heading = jointext(stringof((30-lengthtext(name))/2,Chr$(255)), name) < This centres name in the 30 character field "heading"> Ex. Heading =Jointext(stringof(40-lengthtext(name),Chr$(255)),name) < This right justifies name in a 40 character field "Heading"> CAUTION: You can produce any ASCII character ( 0 - 255 ) with the Chr$ function. ASCII characters 0 to 31 are used as printer controls and although you can use them freely on screen you should never include them in a procedure output which will be sent to the printer. The ASCII character 127 is used internally by Easy Base to replace quotation marks within quoted text. It should not be used for any other purpose. Acceptable parameters: Numeric value, field, variable, expression or function returning a number between 0 and 255 - 10 - ................................................................................ CLEAR RECORDS FROM PROCEDURE COMMAND CLEAR RECORDS FROM The "Clear Records From" command clears all records from a form by erasing its data and index files. If you need to erase all records form a form always use the "Clear Records" command rather than "Delete Record" command. It is much faster and does not require the form to be re-packed afterward. Ex. If input.confirm = "yes" then clear records from invoices - 11 - ................................................................................ CLEARFIELDS FIELD CONTROL CLEARFIELDS The "Clearfields" control blanks out all other fields on a form or procedure input screen whenever the field containing the control is changed. The primary use for the "Clearfields" control is to force default lookups to re-derive for procedures which edit data. For Example - If you wrote a procedure to alter customers addresses and telephone numbers then you would have three fields on the input screen. Accountno derived lookup(customers,accountno) address derived default(lookup(customers,address)) Tel derived default(lookup(customers,tel)) When you enter a customers account number, his old address and telephone number will be looked up, but the "Default" function will allow you to edit them. The procedure code would then update the record with the new address and phone number. The problem is, that having entered an account number and looked up the other fields, if you then realize you have entered the wrong account number and change it, the other fields will not re-derive because they now have a default value. To get them to lookup based on the new account number you would have to take the cursor to each field and clear it with the F6 key. you can automate this process by adding the "Clearfields" control to the accountno derivation:- lookup(customers,accountno)[clearfields] Note: The "Clearfields" control will only work when appended to a derivation which is already dependent on the value of the field. If you want to clear fields based on a change to a field which has no other derivation then you must derive the field with its own name plus the "Clearfields" control. In a field "Number" the derivation - Number[clearfields] - will clear all other fields when the "Number" field is edited but the derivation [clearfields] will have no effect. - 12 - ................................................................................ Close File Command Close The "Close" command closes a non Easy Base file which has been opened with the "Open" command. If a procedure only opens a single external file then the close command can be ommitted, but if several files are opened within a procedure then each must be closed before the next is opened. You can only have one external file open at any one time. .................... declare output fields Files : Path end declare variables Ln as text end Open "C:\CONFIG.SYS" do Read line to Ln if lefttext(Ln,6) = "files=" then exit do Loop Files = Ln Close Open "C:\AUTOEXEC.BAT" Do read line to Ln if Lefttext(Ln,4) = "path" then exit do Loop Close Path = Ln Print List Items See also: Open, Seek, Read, Write, Find, Erase - 13 - ................................................................................ COPY ALL FROM PROCEDURE COMMAND COPY ALL FROM When you are transferring values from the fields of one form to the fields of another in preparation for a "New Record" or "Update Record" you can use "Copy All From" to save time. Ex. for invoiceitems new record invoiceitems.item = input.item invoiceitems.price = input.price invoiceitems.quantity = input.quantity invoiceitems.vatrate = input.vatrate next can be replaced with:- for invoiceitems new record copy all from input next The data transfer in a "Copy All From" is based on the source and destination fields having the same name. If the source and destination fields have different data types Easy Base will attempt a conversion. The conversion will succeed between different numeric types and from numeric to text. It will fail from text to numeric and between any mismatch of date and time. See also:- Derive Off, Duplicate Prevention - 14 - ................................................................................ CREATE TEMP AS PROCEDURE COMMAND CREATE TEMP AS The "Create Temp As" command makes a temporary form during a procedure. There are two main uses for the temporary form. 1. To physically sort the records in a form. Ex. create temp as addresses index off:pause off:escape off:derive off for addresses with surname in order for temp new record copy all from addresses next next rename temp as addresses The other main use for the temporary file is to save time when the majority of records in a form are to be deleted. Ex. A purchases ledger form is to be cleared down at the year end but any unreconciled payment entries are to be retained and flagged as last years. If there were 15000 records in the form of which only 50 were unreconciled then the procedure for purchases if purchases.reconciled = "yes" then delete record else purchases.lastyear = "yes" update record end if next would not only take a very long time to run but would leave the form full of deleted records and it would have to be re-packed. The same result can be achieved very quickly and without the need to re-pack with the following procedure. create temp as purchases for purchases with reconciled = "no" for temp new record copy all from purchases temp.lastyear = "yes" next next rename temp as purchases Do not use the "Clear Records" command to wipe the old file before using "Rename Temp As". If you suffered a power failure after clearing and before renaming you would lose your data. - 15 - ................................................................................ CURRENT DERIVATION PREFIX CURRENT In Easy Base, fields are not normally derived when a previously entered record is viewed on screen. You can force a field to be rederived when viewed by prefixing the derivation with "current". Ex. An invoice form has three fields Ivdate,Cdate and age. Ivdate is derived as "system date" Cdate is derived as "current system date" age is derived as jointext(Cdate-Ivdate," Days old") Each time a record is viewed the Cdate field is rederived with the current system date and the age field shows the number of days since the record was filed. Ex. A form is used to price a certain job. It consists of fields for materials and their prices which are looked up from a "stock" form. If the primary lookup fields are prefixed with "current" - "current lookup(stock,item)" then each time a record is viewed it will reprice the job using the latest prices from the "stock" file. Please note that in the case of lookups, prefixing a secondary lookup has no effect. In the above example "current lookup(stock,price)" would not work Provided that the primary lookup field is prefixed with "current" then any secondary lookups will also be rederived. The values of "Current" fields are also rederived whenever they are accessed in procedure code but they are NOT REDERIVED when they are accessed by a Lookup function. 1. You can have more than one prefix on any one field derivation provided there is a space between each. - 16 - ................................................................................ Current Record SYSTEM VALUE Current Record Whenever a procedure is processing a "For" loop the number of the record being processed is available in the system value "Current Record". NETWORK VERSIONS From the release of Network V1 the system value Current Record has been replaced by a pseudo field Form.Record Number - 17 - ................................................................................ Cursor FIELD CONTROL Cursor The field control "Cursor" sends the cursor to the named field. if(condition,fieldname[cursor fieldname2],fieldname) The cursor control can also be embedded in the beep control. See also "Beep" - 18 - ................................................................................ Cur_Fld SYSTEM VALUE Cur_Fld The System value Cur_Fld holds the number of the field which the cursor is in. The Cur_Fld value can be used in form and input screen derivations to create pop up prompt fields or to change additional help fields. if(Cur_Fld > 2,"Enter the price",blank) If(Cur_Fld < 7,"Help for first six fields",Help for later fields") The Cur_Fld value can also be used to delay lookups when a screen first loads. if(Cur_Fld < 5,blank,lookup(customers,name)) In the case of input screens the value is also carried forward to the procedure code so you can tell which field the cursor was in when the procedure was run. - 19 - ................................................................................ Data Import UTILITY Data Import There are four Data Import routines in Easy Base. The first imports data from files which conform to the Xbase standard first introduced by Dbase. The second imports data from "Comma Delimited" files. This is the standard used by most sequencial access and non relational data systems. The third routine imports data from fixed length record text files. This is the format which is used by all systems to output data. You can therefore import data from virtually any other system by first reporting on it to a disk file and then importing it with the fixed length ASCII import routine. The fourth routine imports one ASCII line per field. XBASE If the data you wish to import is in Xbase format, Easy Base will report the structure of the file. You can print this out by pressing F10. You now design a form to hold the imported data. The names that you give to the Easy Base fields do not have to be the same as the Xbase field names but they must be in the same order. If the Xbase fields are text then the Easy Base fields must be the same length. If the Xbase fields are numeric then the Easy Base fields must be numeric. It does not matter if they are different lengths and any of the three types, integer, fixed point or floating point can be used. If an Xbase field is a date field then it must be imported to an eight character text field in Easy Base. You can reformat it to an Easy Base date field after importation (See the Method - Data Type Conversion). Easy Base does not support "Memo" data. If the Xbase file has a memo field you can still import from it but the memo field itself will be skipped by the import routine. COMMA DELIMITED To import data from comma delimited files all you have to do is design a form with the same number of fields as there are in the file to be imported. Numeric fields can be any of the three types and text fields should be long enough to hold the longest data expected in the incoming field. If any of the fields to be imported holds a date then it must be imported to a text field. You can convert it to a date later. FIXED LENGTH ASCII To import data from fixed length ASCII files you must design a form in which all fields are text and are exactly the same length and in the same order as the fields in the file to be imported. If the file to be imported has a header you can allow for this by entering the header length in bytes. If you are using the fixed length import routine to import data which you have output as a report from another system then - 20 - ................................................................................ Data Import UTILITY Data Import please note the following points. The output report from the original system should have no headers, footers or left margin and there should be no spaces between fields. When the original system outputs the data it will add a carriage return and line feed sequence to the end of each record. When you design the form to import this data to, you must have a final two character text field to trap this sequence and maintain the same record length. This field can be deleted after importation. All fields are imported as text. If any of them contain numeric values which you wish to convert to numeric fields you must do this after importation. (See Method Data Type Conversion) ASCII LINE The ASCII line import routine imports one line per field from an ASCII text file. If you have one field on the form to which you import then you will have one line per record. If you have two fields on the form to which you import then you will have two lines per record Etc. The import routines are for loading entire data files into Easy Base. For Flexible import and export to any part of any file see the External File Commands documentation. - 21 - ................................................................................ DATE FIELD TYPE DATE Date fields hold dates in the eight character format 11/11/94 The assumed century runs from the first of January 1981 to the 31st of december 2080. You can select whether you wish your dates to be displayed as day/month/year (European) or month/day/year (North American) from the "options" item on the utilities menu. You can perform addition and subtraction operations on date values in days. Ex. System date + 30 Arithmetic operations are only correct for the assumed century. Date fields are automatically checked for validity by the system. Easy base does not provide a ready made ten character date field but you can define one with the "Formatted Text" field type. See Also:- Makedate - 22 - ................................................................................ DATETEXT FUNCTION DATETEXT This function returns the date parameter in the form:- 14th October 1994 or October 14th 1994 depending on the date format selected in the utilities menu. Ex. datetext(system date) Ex. datetext(invoicedate + 30) Acceptable parameters: System date Date field Makedate function Date expression - 23 - ................................................................................ DAYOFMONTH FUNCTION DAYOFMONTH This function returns the day no.(1-31) of the date parameter. Ex. dayofmonth(system date) Ex dayofmonth(registered) Ex. if dayofmonth(system date) = 28 then for statements with settled = "No" statements.overdue = "Yes" update record next end if Acceptable parameters: System date Date field Makedate function Date expression - 24 - ................................................................................ DAYOFWEEK FUNCTION DAYOFWEEK This function returns the day no.(1-7) of the date parameter. Ex. dayofweek(system date) Ex. day = spellday(dayofweek(diary.date)) Ex. The following procedure, if called from a batch execute menu will only run on a Monday. declare output fields takings.date takings.dailytotal end if dayofweek(system date) = 1 then for takings with date in reverse order print list items if system date - takings.date > 7 then exit for next end if Acceptable parameters: System date Date field Makedate function Date expression - 25 - ................................................................................ DAYOFYEAR FUNCTION DAYOFYEAR This function returns the day no.(1-365) of the date parameter. Ex. dayofyear(system date) Ex. days = dayofyear(events.date) Acceptable parameters: System date Date field Makedate function Date expression - 26 - ................................................................................ DECLARE PROCEDURE COMMAND DECLARE If you write a procedure which outputs information to the screen, printer or a disk file you must start it by declaring the output fields. Ex. Declare output fields Input.name Customers.name:customers.address:customers.acno countofinvoices end If fields you intend to print exist in a form then declare them as Formname.Fieldname. Fields from the procedures Input Screen are declared as Input.fieldname. Easy Base will then use the field definitions from the form as defaults when you create the Output Format for the procedure. If a field which you intend to print is to be derived during the procedure and does not exist in a form as "countofinvoices" you can give it any name you wish up to forty characters but the name must not contain a ".". Once you have declared fields you can create the Output Format. If you declare an "Ad Hoc" field such as "countovinvoices" then you must include it in the format in order to define its type and length. If you require variables to store values during a procedure but you will not be out-putting their values then declare them as variables not fields. Ex. declare variables count as number lasttype as text end Unprinted variables can only have one of the two types "Number" or "Text". Field declarations must precede Variable declarations and both must precede executable code. - 27 - ................................................................................ DEFAULT FUNCTION DEFAULT This function returns the parameter if not overridden by user entry. Ex. Default(5) Ex. Default(system date) Ex. Default(lookup(stock,price)) The Default function is only used in form and input screen field derivations - It has no meaning in procedure code. Acceptable parameters Any value, expression, field, variable or nested function of the same type as the field in which it is used. - 28 - ................................................................................ DELAY FIELD CONTROL DELAY The "delay" control suspends processing for a given number of milliseconds. Ex. A procedure screen is used from a startup Batch execute menu as an opening screen to an application and has the following field derivations 1. "Welcome to this wonderful program" [delay 1000] 2. "Copyright Fred blogs"[delay 1000] 3. "Press F2 to start" If the field colours are "text" ,"alt1" or "alt2" then the three fields will "Popup" with a one second delay between each. - 29 - ................................................................................ DELAY PROCEDURE COMMAND DELAY The "Delay" command suspends processing for a given number of milliseconds (1 - 5000). Ex. declare variables x as number : total as number end for customers customers.balance = 0 for invoices with acno = customers.acno display status "Totalling invoices for" + Customers.name delay 200 customers.balance = customers.balance + invoices.total next update record next In the above example, wherever a customer only has one or two invoices to total, the status display would change very quickly. By including a 200 millisecond delay you can ensure that each customers name is on screen at least long enough to be read. Delay values outside of 1 - 5000 are ignored. The delay parameter cannot be supplied as a variable. - 30 - ................................................................................ DELETE CHOICE LISTS UTILITY DELETE CHOICE LISTS If you have Choice lists for choice fields that are no longer required you can delete them with this utility. - 31 - ................................................................................ DELETE RECORD PROCEDURE COMMAND DELETE RECORD The "Delete Record" command is used for the selective deletion of records in a form. Ex. for books with title = input.title delete record next This deletes the single book whose title is that entered on the input screen. for books with author = input.author delete record next This deletes the records of all books by the author entered on the input screen. When you use the "Delete Record" command it has the same effect as deleting a record in data entry. In other words the record is marked for deletion but will not actually be removed from the form until the next re-pack. If you use the "Delete Record" command to delete large numbers of records then you should pack the form afterward. If you wish to delete all records in a form use the "Clear records" command not the "Delete Record" command. - 32 - ................................................................................ DERIVE OFF PROCEDURE COMMAND DERIVE OFF Whenever an Easy Base procedure adds or updates a record then by default it checks the derivations of all fields during an add operation and any fields that are affected by an update operation. In other words, if you update the netprice field in a stock form and that form has derived fields for VAT and grossprice then those fields will also be updated. If the procedure adds or updates all fields then this process is superfluous and simply slows the procedure. You can disable form derivation checking with the "Derive Off" command. derive off for stock with item = input.item copy all from input update record next In the above example a stock form has several derived fields but the input screen also does the same derivations. There is therefore no need to re-check them when the record is updated. As a general rule, in procedures which add or update records from an input screen, you should derive all fields on the input screen rather than during the update process. The user will not notice the time taken to derive fields while he is filling them in but he will notice the time taken to derive them after he has pressed F2 There is no corresponding "Derive On" command. Derivation checking is automatically turned back on at the end of the loop to which is prefixed by the "Derive Off" command. If you require derivation checking to be off for more than one loop in a procedure then you must issue the command before each loop. Note:- If the "Derive Off" command is not issued before a loop then form level derivations override procedure derivations. This is not normally a problem, but can be if you try to change the case of a field for printing. For customers customers.name = proper(customers.name) print list items next If the "Name" field on the "Customers" form is derived as Upper(name) then the procedure will print the customers names in upper case. You can overcome the problem either by cancelling the field derivation or by transferring the customers name to a variable and converting that to proper. - 33 - ................................................................................ Disable DERIVATION PREFIX Disable When an Easy Base procedure has an input screen it can be run by pressing any of the function keys 2,4,5,7,8,9 or 10. Where a procedure only does one job, the fact that all keys will initiate it is not important. The default help line states F2 and you can replace this if you want the user to use another. If however you wish to disable function keys which your procedure does not use you can do so by adding the prefix "Disable" followed by the keys you wish to deactivate in quotation marks. You can also disable the Escape Key. Ex. Disable "5789" It does not matter which field you use the "Disable" prefix in and if the field already has a derivation formula it can follow. Disable "4578910Esc" lookup(units,price) In the above example the only active function key is F2 and the user cannot Escape to the menu. The procedure must be run. Disabling the Escape key is useful where a procedure is part of a chain, ie it is run by the run command from a main procedure or it is on a batch menu. You should be careful not to disable the Escape key on procedures that are stand alone with repeating input screens or on main procedures which run other procedures which in turn run the main procedure. Notes: 1. The Disable prefix has no meaning in data entry forms. 2. There must be a space between "Disable" and the key string. The key string must be in quotation marks. 3. You can combine prefixes in any field derivation provided there is a space between them Ex. Disable "4578910" Help "Press F2 when ready" Start Here See also: "Run" "Fun_Key" "Dissable Control" - 34 - ................................................................................ Disable FIELD CONTROL Disable The Disable control is used to change the disabled function keys from within field derivations in a procedure's input screen. Ex. if(actn = "new",actn[disable "4578910"],actn[disable "2"]) - 35 - ................................................................................ DISPLAY STATUS PROCEDURE COMMAND DISPLAY STATUS If you write a procedure which has no output then Easy Base will display a "Running" flash at the top right hand side of the screen. For procedures which only take a few seconds to run this is sufficient to let the operator know that something is happening. For procedures that take some time to complete you should include a status display showing the progress of the procedure. A status display not only allays the nagging suspicion that the system has hung up but also allows the operator to estimate how long it will take. To create a status display just issue the command "Display Status" followed by a message composed of text in quotation marks and fields or variables concatenated by the + sign. Ex. declare variables x as number : y as number end for employees with taxcode = input.oldtaxcode y = employees.total copies :exit for next for employees with taxcode = input.oldtaxcode x = x + 1 display status "Updating Record" + x + "of" + y employees.taxcode = input.newtaxcode update record next The status line is displayed centrally in a small window when the procedure runs. A Status line can have a maximum of 68 characters. If you create a status line of more than 68 characters then it will be cut short to that length. If you need to display counters in two nested loops then write the full display line in the outer loop and "display status" with no parameters in the inner loop. for customers x = x + 1 - loop statements - display status "Checking Invoice" + y + "of customer" + x for invoices with customer = customers.name y = y + 1 - Loop Statements - display status next next - 36 - ................................................................................ DO..LOOP PROCEDURE COMMAND DO..LOOP The "Do" loop structure is used in the Easy Base Procedure code to repeat a series of commands or statements. Whenever a procedure reaches a "Do" command it repeats all the statements between the "Do" and the "Loop" statements until the loop is broken by an "Exit Do" command. Ex. The following code fragment prints 10 copies of the top fifty best selling items in a "stock" form. .........................code............................ declare output fields stock.item : stock.sales : today end Declare variables copies as number : items as number end today = system date do copies = copies + 1 :items = 0 if copies = 11 then exit do bold on print report header bold off for stock with sales in reverse order items = items + 1 if items = 51 then exit for print list items next bold on : print report footer : bold off page feed loop ............................format..................... .Report Header ═════════════════════════════════════════════════════ Top 50 Best Selling Items {today field} ═════════════════════════════════════════════════════ .list items {stock.item field} {stock.sales field} .Report Footer ═════════════════════════════════════════════════════ .end - 37 - ................................................................................ Erase File Command Erase The "Erase" command erases files. It accepts wildcard parameters. ex. Erase "C:\letters\*.doc" Ex. index off For faxlog with date < system date - 30 erase jointext("C:\Fax\",faxlog.filename) delete record next - 38 - ................................................................................ ESCAPE ON/OFF PROCEDURE COMMAND ESCAPE ON/OFF When a procedure is running in Easy Base, it cannot, by default, be interrupted. If your procedure simply lists data to the screen then the end user may not need to see the entire output. You can allow the user to terminate a procedure prematurely with the "Escape On" command. If a procedure performs several tasks, you can selectively enable and disable the Escape Key with the commands "Escape On" and "Escape" Off" Ex. For newpayscales print list items next escape off for newpayscales for employees with scale = newpayscales.scale employees.rate = newpayscales.rate update record next next escape on for employees print newpay next In the above example a procedure lists new pay scales, updates the "Employees" form with the new pay rate and then lists the employees with their new rates. While the procedure is running the operator can terminate it during either of the lists but he cannot terminate it during the process of updating the "Employees" form. See also: Pause On/Off - 39 - ................................................................................ EXIT BATCH MENU PROCEDURE COMMAND EXIT BATCH MENU The "Exit Batch Menu" command terminates a current Batch Menu and returns control to the menu which called it. Ex. An application starts with a batch menu which calls a second batch menu which performs regular daily tasks. It then calls the main user menu. If the application is exited after the daily tasks batch menu has been completed then you will not wish to re-run the daily tasks when the application is restarted on the same day. To prevent this create a form "tasksdone" with a single field "date" and enter a single record with yesterdays date. The first procedure on the daily tasks batch menu would then be for tasksdone if tasksdone.date = system date then exit batch menu else tasksdone.date = system date update record end if next - 40 - ................................................................................ EXIT PROCEDURE PROCEDURE COMMAND EXIT PROCEDURE The "Exit Procedure" command terminates a procedure. Ex. if input.codeword <> "fred" then exit procedure for staffconfidential with name = input.name print list items next If the procedure has a repeating input screen then this is cancelled by the "Exit Procedure" command. - 41 - ................................................................................ File_Date File_Time System value File_Date File_Time Whenever you open a non Easy Base file it's date and time stamp are available in the system values File_Date and File_Time. - 42 - ................................................................................ File_Len System Value File_Len Whenever you open an external file with the "Open" Command the length of the opened file is available in the system value File_Len. The main use of the File_Len value is to position the Read/Write pointer ready to append data to an existing file. ................... declare variables CR as text end CR = jointext(chr$(13),chr$(10)) Open "C:\WP\Address.txt" seek File_Len + 1 Write Input.name Write CR Write Input.address1 Write CR Write Input.address2 Write CR Write input.address3 Write CR Close - 43 - ................................................................................ File_Pos System Value File_Pos Whenever you open an external file with the "Open" command, The position of the file read/write pointer is available in the system value File_Pos. The value is in bytes counting the first byte as 1. The File_Pos value is used to make relative movements of the Read/write pointer with the "Seek" command ..................................... Open "C:\myfiles\Report.txt" for reports new record Read 10 to reports.CustNo Seek File_pos + 3 read 20 to reports.Custname next close ...................................... You can move the Read/Write pointer in either direction. Seek File_Pos - 20 If you move the pointer back beyond the start of the file Easy Base generates the error message "Seek Value < 1 !". See Also: Open, Close, Seek, Read, Write - 44 - ................................................................................ Find File Command Find The "Find" command is used to select non Easy Base files using a wildcard Spec. When you issue the command Find with a wildspec parameter, Easy Base finds the first file to match the spec and puts its name in the Found_File system value. If you then repeat the command without a spec, Easy Base finds the next matching filename and puts it in Found_File. When all files have been found the Found_File value reverts to "Blank". Example: A company recieves replacement part orders by FAX. After the Address lines the FAX is formatted in four lines "Order Start:" - Engineers ID - Part ID - Quantity. All incoming FAXes for parts are filed in C:\FAX\ORDERS. The following procedure imports the orders to the Orders form and then deletes the FAX files. Declare variables Ln as text end Find "C:\FAX\ORDERS\*.*" Do If Found_File = Blank then exit do For orders new record open jointext("C:\FAX\ORDERS\",Found_File) Do Read line to Ln If Ln = "Order start:" then exit do Loop Read line to orders.engineer Read line to order.part read line to orders.quantity next Close Find Loop Erase "C:\FAX\ORDERS\*.*" - 45 - ................................................................................ FIXED POINT FIELD TYPE FIXED POINT Fixed point fields can display up to 14 digits. They use the comma for thousands separation and the full stop for decimal separation. There are no rounding errors with fixed point fields. See - Operators arithmetic - 46 - ................................................................................ FLOATING POINT FIELD TYPE FLOATING POINT Floating point fields can display up to 14 digits. They do not have a thousands separator. They are left justified. - 47 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT The "For" command initiates loops which load records from forms for processing. All statements between the "For" and the "Next" commands are repeated for each record selected. Ex. For Videos If Videos.price = 2.00 then Videos.price = 2.50 update record next In the above example each record in the "Videos" form is loaded into memory - the price field is checked and if it is 2.00 then it is altered to 2.50 - the record is then updated on disk. "For" loops can be nested to any depth. Ex. for videoprices for videos if videos.price = videoprices.price then print list items end if next next In the above example the outer loop loads each record from the "Videoprices" form. While each of these is in memory it then loads each record from the "Videos" form, tests to see if the price field in "Videos" is the same as the price field in "Videoprices" and if so prints the list items section of the report format (containing the video title field) thus grouping all video titles by price. If a "For" loop is unqualified as above then every record is processed starting at one and advancing in sequence. "For" loops can be qualified in several ways in order to select records in other orders or groups. The following qualifications are available:- For (form) with (fieldname) in order For (form) with (fieldname) in reverse order For (form) with (fieldname) = (value) For (form) with (fieldname) > (value) For (form) with (fieldname) < (value) For (form) with (fieldname) >= (value) For (form) with (fieldname) <= (value) For (form) new record With the exception of the "New Record" qualification, all accept the suffix "Unique" and an "Alias" for the form name. - 48 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by "in order" or "in reverse order" then the records are selected in alphabetical or reverse alphabetical order if the selection field is text, and in numeric or reverse numeric order if the selection field is numeric. Ex. for books with title in order print list items next Ex. for salesmen with monthlysales in reverse order print list items next If a "For" loop is qualified by "in order unique" or "in reverse order unique" then records will be selected in order or in reverse order as above. However, where there are multiple occurrence of the same field value a "unique" loop will only select the first occurrence of each value if the loop is in order or the last occurrence if the loop is in reverse order. Ex. for books with category in order unique count = count + 1 print list items next print categorycount This example prints and counts the different categories in the "books" form. Ex. for theatreseats with price in reverse order unique for seatprices new record seatprices.price = theatreseats.price next next The above example selects one example only of each seat price in an existing "theatreseats" form in descending price order and enters a new record in the new "seatprices" form. - 49 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by a "Fieldname = " condition then only the record or records where the field contents match the condition will be selected. Ex. for books with category = "fiction" print list items next This example prints all books which have the category "fiction" Ex. For customers with acno = input.acno creditlimit = input.creditlimit update record next In this example the procedure has an input screen where the operator enters a customers account number and new credit limit. On pressing F2 the procedure finds the single record in the "Customers" form which has the input account number and updates the "creditlimit" field. - 50 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by a "with (fieldname) > " or "with (fieldname) >= " condition then records are selected in order like the "with (fieldname) in order" qualification but starting with the first record where the field contents match the condition value rather than the first record. Similarly the qualifications "with (fieldname) < " and "with (fieldname) <= " select records in reverse order but starting with the last record to match the condition value. In case you are new to progamming it should be pointed out that the relational operators >, <, >= and <= can be applied to text as well as to numbers. When they are applied to text they relate to the text's alphabetical order. These four qualifications provide the means to select any subset of records which has a range of values in a particular field. Ex. for pupils with age > 6 if pupils.age = 12 then exit for print list items next This example prints the names of all pupils between the ages of seven and eleven. The "For" qualification starts selection in numeric order of age starting at seven and the "Exit for" command terminates the loop when the first pupil aged twelve is loaded. Ex. A "Videos" form has a compound index field called "catorder" which is derived by joining the text of the "category" and "title" fields. The following procedure selects all the videos which have the category entered on the input screen and lists them with the title in alphabetic order. for videos with catorder >= input.category if videos.category > input.category then exit for print list items next Ex. for vehicles with seats > 4 unique count = count + 1 next "count" returns the number of different seat capacities > 4 - 51 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT If a "For" loop is qualified by "new record" then no records are selected from the form. Instead, a new blank record is created at the "For" command, all statements between the "For" and "Next" commands apply to this new record and it is entered to the form at the "Next" command. Ex. for books new record books.dateinstock = system date copy all from input next This example enters a new record to the "Books" form making the "dateinstock" field equal the system date and copying all other fields from the procedures input screen. Ex. declare variables hourcount as number end '..............clear last wages clear records from wagelist '..........count employees hours from timesheet... for employees hourcount = 0 for timesheet with worksno = employees.worksno hourcount = hourcount + timesheet.hours next for wagelist new record wagelist.worksno = employees.worksno wagelist.name = employees.name wagelist.hours = hourcount wagelist.grosswage = employees.wagerate * hourcount next next '.................print the wages list for wagelist with worksno in order print list items next This procedure starts by erasing the present contents of the "wagelist" form. It then selects each record from the "employees" form, counts all entries in the "timesheet" form for the employee and enters a new record in the "wagelist" form. Finally it prints the new wage list. - 52 - ................................................................................ FOR..NEXT PROCEDURE COMMAND FOR..NEXT Occasionally you may need to nest a "For" loop of a form within another "For" loop of the same form. If you do this, then in order to differentiate between the fields in the outer and inner loops you must allocate an "alias" name to the form in one of the loops. When you allocate an alias to a form name then all the fields of the form can also be referenced by the alias name. Ex. .......................procedure code........... declare output fields males : females : pupils.age end for pupils with age in order unique males = 0 : females = 0 for pupils alias agegroups with age = pupils.age if agegroups.sex = "male" then males = males + 1 else females = females + 1 end if next print list items next ....................output format...................... .list items There are {males} males and {females} females aged {pupils.age} .end This example counts and prints the number of male and female pupils in each age group. - 53 - ................................................................................ FORMATTED TEXT FIELD TYPE FORMATTED TEXT If you define a field with the type "formatted text" a window opens up in which you enter the formatting characters. The characters which are to be entered by the user are shown as question marks. Any other characters which you enter become permanent fixtures in the field. The "formatted text" field can be up to twenty characters long and can be used for dates, national insurance numbers etc. Formatted text fields should not be derived. - 54 - ................................................................................ Fun_Key SYSTEM VALUE Fun_Key The Fun_Key system value holds the number of the function key which was pressed to run a procedure from its input screen. If the procedure was started automatically by a [Run] field control then Fun_Key returns the value 0. The main use of the Fun_Key value is to branch from one procedure to another using the Run command. Its value is also available immediately to the input screen field derivations. You can therefore make any field entry mandatory or not depending on which function key was pressed. Example. If you set the Mandatory entry attribute of field "Name" to "If" and enter the condition "Fun_Key = 2 or Fun_Key 4" then the procedure cannot be run with the F2 or F4 keys unless the Name field has been filed but it can be run with the name field blank if any of the other function keys were pressed. The value of disabled function keys is also available to input screen derivations. You can use disabled function keys to redirect the cursor: if(Fun_Key = 7,fieldname[cursor size],fieldname) By using disabled function keys to redirect the cursor to fields on a different page you can make a single procedure appear to have more than on input screen for different purposes. See also "Run" "Disable" - 55 - ................................................................................ GLOBAL NUMBER SYSTEM VALUE GLOBAL NUMBER The variables you create in Easy Base procedures are local variables - they cease to exist when the procedure ends. So that you can pass messages between procedures Easy base provides a single global variable "Global Number". Once you allocate a value to "Global Number" that value remains unchanged (even if your computer has been turned off) until you allocate another value to it. You can of course pass many messages between procedures simply by entering records in forms and re-reading them but using "Global Number" is much quicker. The following example uses "Global Number" to make access to a user menu restricted by password. Two procedures are defined. The first, "getpassword" has a password field on its input screen and the following code:- if input.password = "gingerbread" then global number = 1 else global number = 0 end if The second procedure "checkpassword" has the following code:- if global number <> 1 then exit batch menu global number = 0 The open access menu system has an item for the restricted menu but instead of calling it direct it calls the menu "Password" which is a batch execute menu with the items run procedure ---- getpassword run procedure ---- checkpassword user menu ---- restricted If the password "gingerbread" has not been entered in the "getpassword" input screen then "checkpassword" will return control to the original menu. - 56 - ................................................................................ GOTO (FIELD) NEXT DERIVATION PREFIX GOTO (FIELD) NEXT In an Easy Base form or procedure input screen the cursor moves from field to field in the default order top left to bottom right when you press the Return key. You can alter this default order by deriving any field with "Goto (fieldname) next". If the field from which you wish to redirect the cursor already has a derivation formula you simply add the "Goto - Next" as a prefix. Ex. goto price next goto item next default(lookup(stock,name)) Cursor redirection with the "Goto - next" derivation only occurs when the Return key is pressed it is not activated if you move the cursor with the directional arrow keys. 1. You can have more than one prefix on any one field derivation provided there is a space between each. - 57 - ................................................................................ Help DERIVATION PREFIX Help Easy Base provides a default help line for all data entry and procedure input screens. You can replace the system default help line with your own default help line by pressing F6 during form or screen design. You can also override the default with a field specific help line by using the derivation prefix "Help" followed by the help line in quotation marks. Field specific help lines replace the default help line whenever the cursor is in the field with the derivation prefix. Example: If, in the derivation formula for field "Name" you type:- Help "Enter Customers name and press return" then the help line will show the quoted text whenever the cursor is in the Name field but will revert to the default when the cursor moves to another field. You can use the Help prefix in any or all fields. The help line has a maximum length of 80 characters. If you quote a help line greater than 80 characters it will be truncated at run time. Help lines of less than 80 characters are automatically padded to 80 with space characters. If the field already has a derivation formula, add the help as a prefix:- Help "Enter Part Name" proper(partname) Notes: 1. There must be a space between Help and the quoted line. 2. The line must be in quotes and quotation marks cannot be included in the line. 3. You can have more than one prefix on any one field derivation provided there is a space between each. Ex. Autodial Help "Press Ctrl + D to Dial" lookup(customers,No) - 58 - ................................................................................ HOURS FUNCTION HOURS This function returns the hour ( 1 to 23) of the time parameter Ex. Hour(system time) Ex. Labourcharge = (hours(endtime)-hours(starttime))* rate Acceptable parameters: System time Time field Maketime function Time expression - 59 - ................................................................................ IF FUNCTION IF Ex. if(num >= 0,"Positive","negative") The first parameter of the "if" function is an expression, the second is the value to be returned if the expression is true and the third is the value to be returned if the expression is false. If the third parameter is omitted and the expression is false then the field or variable retains any value it had before the function was called. The "if" function can be extended to a full "case" function simply by adding more expressions and return values. Ex. if(num > 0,"Positive",num = 0,"Zero","Negative") There is no limit to the number of expressions and return values. If more than one expression is true the value returned is that for the first true expression. If none are true then the trailing value is returned as the "case else". Acceptable Parameters: Numeric expressions Quoted text Fields and variables Other nested functions System Values - 60 - ................................................................................ IF THEN ELSE PROCEDURE COMMAND IF THEN ELSE Easy Base recognizes the following "If Then Else" constructions. 1. if (condition) then (action) ------------------------------------------ 2. if (condition) then (action) else (action) ------------------------------------------ 3. if (condition) then (action) (action) (action) end if ----------------------------------------- 4. if (condition) then (action) (action) else (action) (action) end if If then constructions can be nested to any depth. if (condition) and (condition) then (action) if (condition) or (condition) then (action) (action) else (action) if (condition) eqv (condition) then (action) (action) end if if (condition) then (action) else (action) end if Easy base does not interpret "Else if" constructions. Easy base does not interpret two "if" conditions on one line. In the above examples "action" applies to any Easy base command or full "Do Loop" or full "For Next" structure. You cannot split a "Do Loop" structure and you can only split a "For Next" structure by a simple "If then" and "End if" with no "else" command. Ex. on next page. - 61 - ................................................................................ IF THEN ELSE PROCEDURE COMMAND IF THEN ELSE In the following example the procedure has an input screen with a field "type". "type" is a choice field with the choices "In age order", "In alphabetic order" and "By sex". The procedure code selects one of three listings from the "Pupils" form by splitting "For Next" structures by "If Then" structures. if input.type = "in age order" then for pupils with age in order end if if input.type = "in alphabetic order" then for pupils with name in order end if if input.type = "by sex" then for pupils with sex in order end if print list items next next next The structure above is the only one in which "For Next" structures can be split. There must be a separate "If Then" followed by "End If" for each "For". There must be a "Next for each "For" and there cannot be an "Else" anywhere in the construction. - 62 - ................................................................................ INDEX OFF PROCEDURE COMMAND INDEX OFF When an Easy Base procedure adds, updates or deletes a record then by default it updates any affected index files. As the majority of transaction procedures act on a single record this is the fastest method of processing. It does of course require that the form is periodically re-packed. If, however, a procedure is to add, update or delete many records then updating each affected index file on each iteration of the "For" loop can be much slower than ignoring the indices during the loop and re-writing them from scratch afterward. You can take the "re-write" option by issuing the command "Index off" before the "For" loop. index off for employees for wagelist new record wagelist.worksno = employees.worksno wagelist.taxcode = employees.taxcode next next Obviously re-writing an entire index file when only one or two records are affected would be slower than simply updating the indices. The actual point at which "Index Off" becomes faster can only be determined by trial but as a general rule the two systems take equal time when between 12 and 15 percent of the records of a file are affected and the advantage of "Index Off" becomes greater the bigger this percentage. There is no corresponding "Index On" command. Indexing is turned back on automatically and the index files are re-written at the end of the loop to which the command applies. If you require indexing off during more than one loop you must issue the command before each. index off for invoices with date < makedate(05,04,93) delete records next index off for invoices with customer = "john smith" invoices.customer = "John Smith Ltd" update record next If you prefix a loop which deletes records from a form then the form is packed at the end of the loop. - 63 - ................................................................................ INSTALL FORM UTILITY INSTALL FORM The "Install Form" utility is accessed from the Utilities menu. If you wish to include a form in one application which you designed in another then you cannot simply copy the files to the new applications directory as there will be no entry in the "Forms Directory" file. To install a form from another application - first get the forms DOS filename from that application then select "Install Form" from the utilities menu. You will be asked to supply the forms name and the full path and filename from where it is to be copied. Easy Base will then copy the forms definition file and make the appropriate entry in the "Forms Directory". It will then ask whether or not you wish the data to be copied from the original application. If you install a form from another application and it has "lookup" derivations or Choice List fields then you will have to create the relationships and choice lists in the new application. They are not automatically reproduced. - 64 - ................................................................................ INSTALL PRINTER UTILITY INSTALL PRINTER The "Install printer" utility is accessed from the Utilities menu. It can also be included in a user menu so that end users of finished applications can install their printer without access to the system menus. When you select "Install Printer" you are offered a choice of ten printer drivers - five for named printers and five generic drivers. The drivers supplied cover virtually all printers except daisy wheels. When you have chosen your printer driver Easy Base then asks for the parallel port number to which printer output is to be sent - one or two. Easy Base does not have a built in serial port printer output. If your printer only has a serial input then you must redirect a parallel port with the DOS "Mode" command. Easy Base then asks how much paper wastage there is at the top of the page. This is quite important as it is used in conjunction with the paper size in use to determine the "Bottom Margin" value, used for pagination in printed reports. You are then asked for the wastage at the left margin. This information is used in the format editor to display the right edge of the paper at the different print sizes. There are only two settings for wastage at the left margin - zero and ¼ inch. If you have a cartridge fed printer such as a Laser or Bubble Jet then it will almost certainly be ¼ inch. If you have a traction or manual feed then you will be able to adjust the left margin and you should set it to zero. Finally you are asked for the top margin required in printed reports. This can only be between the value set as the printers inherent wastage and one inch. The value you set here will be the default top margin for all printouts. If you need a larger top margin for certain printouts then you must include blank lines in the page header of the procedures output format. - 65 - ................................................................................ INSTALL PROCEDURE UTILITY INSTALL PROCEDURE The "Install procedure" utility is accessed from the Utilities menu and allows you to install procedures which you designed in another application. It is used exactly like the "Install Form" utility previously described. - 66 - ................................................................................ INTEGER FIELD TYPE INTEGER Integer fields can have up to 14 digits and use a comma as the thousands separator. Although integer fields display integers they store the result of any division correct to 15 significant figures. See - Operators Arithmetic - 67 - ................................................................................ INTEXT FUNCTION INTEXT This function returns the starting character number of one length of text within another. Ex. Intext(companyname," and ") < Returns 7 if companyname = "Turner and Smith" The intext function returns 0 if the second parameter is not found in the first. Ex. If intext(customers.companyname," Ltd") <> 0 then customers.Ltd = "yes" update record end if Acceptable parameters: Text field/variable Any function returning a text value - 68 - ................................................................................ JOINTEXT FUNCTION JOINTEXT Ex. Jointext(forename,surname) The jointext function takes any number of parameters. Ex. Jointext("Mr ",forename," ",surname," Esq.") Ex. Jointext(if(sex=male,"Mr ","Ms "),surname) The "jointext" function will also perform any arithmetic operations required for a text output. Ex. Jointext("The Gross price is ",netprice *120/100) The "jointext" function is also used for creating compound index fields. Ex. Jointext(reverse(zeropad(price,4,2)),stockitem) This creates a field whose index allows a stock file to be listed with stockitem in alphabetical order but grouped by price in reverse order. If you "Jointext" a date or time field you will get the date or time's numeric value not the date or time's string representation. (See Compound Index) By default the "Jointext" function strips trailing blank space characters from the texts being joined. If you wish to join text in a Text Block field so that the second text starts on a new line then you can force this by including Chr$(13) which Easy base uses as a "New Line" flag. Ex. Jointext(block1,chr$(13),block2) <The text from block2 will start on a new line> Ex. Jointext(block1,chr$(13),chr$(13),block2) <There will be a blank line between block1 and block2> Acceptable Parameters: Numeric expressions Quoted text Fields and variables Other nested functions System Values - 69 - ................................................................................ LEFTTEXT FUNCTION LEFTTEXT This function returns a specified number of characters from the start of a field or variable. Ex. Lefttext(salutation,2) < returns "Mr" from "Mr Smith" or "Ms" from "Ms Smith" Ex. Lefttext(datetext(system date),4) < Returns 12th , 20th Etc.> (European date format) Acceptable parameters: Text field/variable Any function returning a text value - 70 - ................................................................................ LENGTHTEXT FUNCTION LENGTHTEXT This function returns the length of text in a field or variable Ex. lengthtext(name) < Returns 4 if name = "Bill" 10 if name = "Bill Smith" Acceptable parameters: Text field/variable Any function returning a text value - 71 - ................................................................................ LIST FILE NAMES UTILITY LIST FILE NAMES When you give a name to a Form or Procedure in Easy Base it is not the actual DOS file name used. A Form has separate DOS files for its definition, its data and its indices. Similarly a procedure can have one or two files depending on whether or not it has an input screen. Within an application you never need to know the DOS filenames as Easy Base takes care of all disk activity in the background. You may however need to know the DOS filenames if you intend to export data to another program or copy a Form or Procedure from one application to another. You can list the DOS filenames for Forms and reports with the "List File Names" utility. The "List File Names" utility can also be called from a user menu without accessing the system. - 72 - ................................................................................ LIST VARIABLES PROCEDURE COMMAND LIST VARIABLES The "List Variables" command is a de-bugging facility for developers. If a procedure is not producing the expected results you can interrupt it at any point and view the contents of all fields and variables in memory at that point. Ex. for authors with surname in order if authors.surname = "kippling" then list variables for books with author = authors.surname print list items next next - 73 - ................................................................................ Lock / Unlock Procedure Command Lock / Unlock Network versions only Easy Base automatically places protective locks whenever any process might cause a multiple access confliction. You do not have to use the Lock and Unlock commands to avoid such conflicts. There will however be occasions where multiple access would prevent the correct execution of an application even though no access conflict would occur. For Example, if a procedure collects statistics from form a and form b then updates form c with the information, you might not wish to have any other workstation access form c until the update is complete. Easy Base would not automatically prevent access to form c while your procedure read from a and b as this would not cause any access conflicts. To prevent access in such cases use the Lock and Unlock Commands. Declare variables Count as number end Lock Stats : Lock Stock for Stock with Location = input.location delete record next Pack Stock Unlock Stock For stock count = stock.total records exit for next for stats stats.stockitems = count update record next Unlock stats Locks placed with the Lock command are total locks. There is no option to place read only locking. Once a lock command has been executed it remains in force until the same workstation issues an unlock command for the form. If you need a form to remain locked between procedures you can issue the Lock command in one procedure and the Unlock command in another. In such cases you need to ensure that there is no way to avoid running the procedure with the Unlock command. Similarly, when the Lock and Unlock commands are within a single procedure, there must not be any option to exit the procedure between the two commands. - 74 - ................................................................................ LOOKUP FUNCTION LOOKUP Ex. Lookup(customers,surname) Lookup(customers,surname,address) The first parameter is the name of a relationship defined in the relationships form. The second parameter is the name of the field in the secondary file whose value is to be looked up. The third parameter, which is optional, specifies that a field other than the looked up field is to be listed for selection when there is more than one match. (The looked up field must be indexed) The parameters are absolute. You cannot supply them as variables, fields or the results of other functions. In the first example, if you enter "Sm*" in the field with the derivation and there are several names beginning with Sm, then the names will be listed for you to choose from. This is most useful if all the names are different. If, however, there are several names beginning with Sm but they are all "Smith" then it will be more useful to use the second example which will list all the addresses for the "Smith" names. Lookup functions which have two parameters do not list duplicates when there is more than one match, whereas lookup functions with three parameters do. You can therefore use one lookup of the first type and one lookup of the second type to resolve ambiguities. For example, to perform lookups from the customers form (which has duplicates in the "Surname" field), you would firstly create an additional invisible field on the input screen (surname2) derived surname. You then enter two relationships between the screen and the customers form. The first, called customers, links surname with surname and the second, called customers2, links surname2 with surname. The surname field on the input screen is derived - Lookup(customers,surname). All the other fields to be looked up are derived - Lookup(customers2,fieldname) but one of them (Any one) has the third parameter added :- Lookup(customers2,forename,address) When you enter "Sm" in the surname field, the first lookup will list all the customer surnames beginning with "Sm". When you choose "Smith" from this list, "Smith" will be copied to the invisible field Surname2 and the second lookup will trigger listing all the addresses of the Smith customers. - 75 - ................................................................................ LOWER FUNCTION LOWER Returns the lower case of the parameter. Ex. lower(partname) Ex. Heading = Jointext("List of parts supplied by ",lower(makers. name)) Acceptable parameters: Quoted text Text Field/variable - 76 - ................................................................................ MAKEDATE FUNCTION MAKEDATE The Easy base Date field is a six digit field which covers the century from 1 Jan 1981 to 31 Dec 2080 and upon which you can perform addition and subtraction operations in days. Ex. Duedate = Invoicedate + 30 In order to provide this facility the date is held as a numeric value. You can only type a date directly into a date field. If you need to enter a date in a derivation or in a procedure code you must use the "makedate" function. Ex. Makedate(5,11,95) < Returns 05/11/95 > Ex. Nextmonth = month(system date)+1 if nextmonth = 13 then nextmonth = 1 Nextdelivery = makedate(1,nextmonth,year(system date)) < Returns the date of the first of next month > Acceptable parameters Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 77 - ................................................................................ MAKETIME FUNCTION MAKETIME The value of a time field in Easy base is held as a numeric value representing the number of seconds past midnight. This allows you to perform addition and subtraction operations in seconds. Ex. Timetaken = Round(Endtime - Starttime / 60) < This returns the time taken in minutes > You can only enter a time directly into a time field. If you need to enter a time in a field derivation or in a procedure code you must use the "Maketime" function. Ex. Time = Maketime(20,20,00) < returns 20:20:00 > Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 78 - ................................................................................ Manual Feed Procedure Command Manual Feed When you set the paper type in use from the utilities menu, you tell Easy Base whether you have a manual or continuous feed printer. This is then used throughout all procedures. If your printer has a continuous feed but a particular procedure requires manual eg label or envelope printing you can force Easy Base to pause after each page without changing the default by issuing the Manual Feed command within the procedure code. Declare output fields mail.name :mail.address end manual feed for mail print envelope next - 79 - ................................................................................ MATHS FUNCTION MATHS The following maths functions can be used anywhere in Easy Base code or field derivations. Log() Log10() Sqrt() Sin() Cos() Tan() Atan() Deg_rad() converts degrees to radians Rad_deg() converts radians to degrees Exp() Raises e (the base of natural logarithms) to the power of the parameter Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any function returning a numeric value - 80 - ................................................................................ MENU CALLS MENU FUNCTION MENU CALLS In addition to the utilities which can be called from user menus the following calls can also be made. Run Procedure : Runs a pre-defined procedure Last Output : Recalls the output from a procedure Data Entry : Allows direct access to a form User Menu : Calls another user menu System Menus : Calls the Easy Base System menus Exit to DOS : Terminates Easy Base Run external program : Shells to another program Notes: You cannot run another major program from within Easy Base - there is very little spare memory - this call is however useful for calling batch files which export data to other programs for later use. If you place "Exit to DOS" on a normal menu the user will be asked to confirm that he wishes to exit. If you place it on a batch execute menu he will not. - 81 - ................................................................................ MIDTEXT FUNCTION MIDTEXT This function returns a given number of characters from a given starting point in a field or variable. Ex. Midtext(fileref,4,4) < Returns "Bill" from "TR/Bill/1243" Acceptable parameters: Text field/variable Any function returning a text value - 82 - ................................................................................ MINUTES FUNCTION MINUTES This function returns the minutes number from the time field parameter. Ex. minutes(system time) Ex. Unitsperhour = rounddown(60/(minutes(endtime)- minutes(starttime))) Acceptable parameters: System time Time field Maketime function Time expression - 83 - ................................................................................ MOD FUNCTION MOD The Mod function returns the remainder of an integer division Ex. surplus rounds = mod(bullets,soldiers * ammoissue) Ex. for Historydates historydates.leapyear = "No" if mod(historydates.year,4) = 0 then historydates.leapyear = "Yes" update record end if next < This example fills a new field "leapyear" which has been added to the "Historydates" form after data has been entered> Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 84 - ................................................................................ MONTH FUNCTION MONTH This function returns the month number from a date parameter. Ex. Month(system date) Ex. Month(birthdate) Acceptable parameters System date Date field Makedate function Date expression - 85 - ................................................................................ MULTIPLE LINES PROCEDURE COMMAND MULTIPLE LINES You can place multiple code instructions on a single line of the code editor by separating them with a colon. Ex. for ledger with lineno = input.lineno ledger.net = input.net:ledger.vat = input.vat ledger.acno = input.acno:ledger.gross = input.gross update record next - 86 - ................................................................................ Next Batch Procedure Command Next Batch The Next Batch command resets the batch menu pointer from within a procedure. By using the Next Batch command you can cause a batch of procedures to repeat without returning to the menu in the same way that you can repeat a single procedure with an input screen. For example, in a variable length invoicing system you might have three procedures on a batch menu. The first procedure selects the current customer and invoice number, the second writes invoice lines to the lineitems form and the third prints the invoice. To have the entire process repeated after each invoice the last line of the last procedure should be: Next Batch 1 Similarly, if the user inadvertently selects the wrong customer in the first procedure you can use an input screen field in the second to return to the first. If input.reselect = "yes" then Next Batch 1 By using Next Batch and Global Number you can create a batch menu which reacts like a single procedure repeating until the Escape key is pressed. To exit from a batch of procedures on the escape key, set Global Number to 1 in the first procedure and to 0 in the last. Add a procedure immediately after the first with the code:- If Global Number = 0 then exit batch menu - 87 - ................................................................................ ODD/EVEN PAGE PRINT PROCEDURE COMMAND ODD/EVEN PAGE PRINT If you want to print procedure output on both sides of the paper then, unless you have a VERY expensive printer, the only way to achieve this is to print the odd pages, turn your paper over and print the even pages. For a short report you can do this by setting paper feed to "Manual" from the utilities menu and turning each sheet over individually. If, however, you have a cut sheet feeder or a cartridge fed printer it is much quicker to print all the odd pages, turn them all over and then print all the even pages. To do this in Easy Base, issue the command "Odd Page Print" immediately before the print output and "Even Page Print" immediately after it. declare output fields customers.name : customers.phone end odd page print for customers with surname in order if bottom margin < .75 then page feed print list items next even page print '(Easy Base pauses here till you are ready) If you will be binding your printed report down the left hand edge then you will need a larger left margin on the odd pages than on the even. If you start odd page printing with the command "Odd Page Print for Binding" then Easy Base will add half an inch to the format left margin when it prints the odd pages. You cannot alter the fixed value of half an inch but if you create your output format with a left margin of half an inch(suitable for the even pages) and print for binding, you will find that the resulting output suits most office binding systems. If you use Odd/Even page printing then it is important that you realize it is achieved by running the portion of your procedure between the "Odd Page Print" and "Even Page Print" twice - simply cancelling output to the printer at the appropriate times. You must not, therefore include any commands which add, update or delete records between the commands. If you do they will be performed twice. Similarly if you are running a loop counter during the print process then it will have double the expected value after the "Even Page Print" command. The system value "Page Number" is reset to one at the "Even Page Print" command. If you include two separate sets of odd and even printing in a single procedure then you cannot use "Page Number" to number the second printout. You can of course use an ad hoc field. Just remember to reset it to one immediately before the "Even Page Print" command. - 88 - ................................................................................ ODD/EVEN PAGE PRINT PROCEDURE COMMAND ODD/EVEN PAGE PRINT This document was written in an Easy Base form "Ref" which has fields "Name", "Type", "Pageno" "Topicpage" and three text block fields "T1", "T2" and "T3". Each record holds one page of the reference manual and the pages were entered in no particular order. The following procedure was used to number, sort, index and print the manual. declare output fields ref.name : ref.type :ref.pageno ref.t1 : ref.t2 : ref.t3 : type : name end declare variables x as number : y as number namelen as number : typelen as number end '.....................NUMBER REFERENCE PAGES.... for ref with name in order y = ref.total records x = x + 1 display status "Numbering page" + x + "of" + y ref.pageno = x : update record next odd page print for binding '...............PRINT COMMANDS INDEX........ bold on : print commandheader : bold off for ref with type = "procedure command" subindex name next for ref with subindex in order if topicpage > 1 then skiprec 'only first page ref.name = proper(ref.name) 'of each topic listed print commands next '..............PRINT SYSTEM VALUES INDEX... bold on : print valuesheader : bold off for ref with type = "system value" subindex name next for ref with subindex in order if topicpage > 1 then skiprec ref.name = proper(ref.name) print values next page feed '... eject first index page '......... subsequent index pages are produced with exactly '........ the sane code as above and are omitted for clarity. '......................MAIN REFERENCE............. page feed for ref with name in order namelen = lengthtext(ref.name) - 89 - ................................................................................ ODD/EVEN PAGE PRINT PROCEDURE COMMAND ODD/EVEN PAGE PRINT typelen = lengthtext(ref.type) '....centre justify type type = jointext(stringof((20-typelen)/2,chr$(255)),ref.type) '....right justify name name = jointext(stringof(20-namelen,chr$(255)),ref.name) bold on : print refheader : bold off 'type and names fields print list items 't1, t2, t3 bold on print reffoot 'pageno page feed next even page print - 90 - ................................................................................ Open File Command Open The "Open" command is used to open a non-Easy Base file. The filename can be supplied within quotes or as a text variable or field. The filename can include a complete path. If a path is not included then the file is opened in the Easy Base System directory. If the filename supplied is that of an existing file, that file is opened for access with the Read or Write commands. If there is no file with the supplied filename then a new file with that name is created and opened. Ex. Open "C:\config.sys" Open Files.name Open jointext("C:\wp\",input.filename) See also: Close, Seek, Read, Write, Find, Erase - 91 - ................................................................................ OPERATORS ARITHMETIC OPERATORS The arithmetic operators used in Easy Base are:- + (addition) - (subtraction and negation) * (multiplication) / (division) ^ (exponentiation) Where there are multiple operators in a derivation they are processed with the following precedence:- 1. Exponentiation 2. Negation 3. Multiplication and Division 4. Addition and Subtraction The default precedence can be altered by the use of parenthesis. Easy base does not perform Integer Arithmetic. When a numeric field shows the result of a division the "displayed" number is "truncated" to an integer in Integer fields and has the last digit rounded in Fixed Point and Floating Point fields. The "Stored" value which is used in any further calculations, however, is always correct to 15 significant figures irrelevant of field type. For Example: If three fields x,y and z are defined as integers, y is derived as x/4 and z is derived as y*4 then when 9 is entered in x, y will derive as 2 but z will derive as 9 because it multiplies the "stored value" of 2.25 by 4 not the displayed value of 2. Handling arithmetic in this way means that, by default there are no rounding errors. This has several advantages not least that currency values need only ever be defined to two decimal places. It does mean, however, that should you require rounding errors to be carried forward you must use one of the Easy Base rounding functions on the result of any intermediate division. In the above example, had y been derived as "round(x/4)" then z would derive as 8. If a fourth field had the formula:- if(y = 2,5,10) it would only derive as 5 if y had been rounded or if 8 had been entered in x. - 92 - ................................................................................ OPERATORS RELATIONAL OPERATORS Easy base uses the following relational operators:- = (equals) <> (not equals) > (greater than) < (less than) >= (greater than or equal to) <= (less than or equal to) Where relational operators and arithmetic operators are both used in a derivation. Relational operations are performed after arithmetic operations. Text values are not case sensitive when compared with relational operators. When you compare two fields or variables, the comparison is numeric if both are numeric and alphabetic if both are alphabetic. If you compare a numeric field with a text field then Easy Base will test to see if the text field contains a number. If it does then the comparison will be numeric and if it does not then the comparison will be alphabetic. WARNING If you compare the result of a division in an integer or fixed point field using the = or the <> operators, then the value used for comparison will be the (accurate) stored value and not the "Truncated" number shown in the field. See Operators Arithmetic - 93 - ................................................................................ OPERATORS LOGICAL OPERATORS Easy Base uses the following logical operators:- 1. and 2. or 3. xor (or exclusive) 4. eqv (equivalence) 5. imp (implication) Logical operations are performed last after arithmetic and relational. Logical operators return a boolean (true or false) value from two other boolean expressions. Ex. If(x = y and a = b,"Yes","No") < Yes is returned if both expressions are true > If(x = y or a = b,"Yes","No") < Yes is returned if either or both expressions are true > If(x = y xor a = b,"Yes","No") < Yes is returned if either one but not both are true > If(x = y eqv a = b,"Yes","No") < Yes is returned if both expressions are true or if both expressions are false > If(x = y imp a = b,"Yes","No") < Yes is returned for all combinations except the first expression being true and the second false > The Easy Base logical operators are primarily designed for use within the "If" function and in "If..Then..Else" constructions where their actual value is not relevant. If you wish to use the value of "false" in formulae then it is represented by 0. True can be represented by any value other than 0 and should not be used. - 94 - ................................................................................ OPTIONS UTILITY OPTIONS The "Options" utility allows you to select your preferred date format (European or North American). You can also enable or disable screen saving. If enabled the screen saver operates after three minutes without a keypress during record entry, procedure screen entry or if a menu is being displayed. The screensaver is never invoked while a procedure is running or while reformatting or re-indexing is taking place. The third option allows you to select the border style you require around menus and message boxes. Option four selects whether or not screen clearing will be done with the curtain effect. Option five selects the COM port which will be used during Auto-dialling. The options utility can be called as a user menu function so that an end user can access the facilities without access to the system menus. - 95 - ................................................................................ OUTPUT SYSTEM VALUE OUTPUT When a user runs a procedure his choice of output, screen, printer or disk is stored in the system value "Output". By accessing the system value "Output" you can make your procedure do different things depending on where the output is to be sent. Ex. if output = "screen" then print heading1 else print heading2 end if - 96 - ................................................................................ Pack Procedure Command Pack Network Versions only. Form packing can be initiated within a procedure. Syntax: Pack formname Example: Lock Clients For Clients with name = input.name delete record next Pack Clients Unlock Clients The Pack command must not be executed within a for or do loop. - 97 - ................................................................................ PACK DATA FORMS UTILITY PACK DATA FORMS The process of actually removing a deleted record from a form is time consuming. What actually happens is that all the "Live" records are copied to a temporary file. The original file is then deleted and the temporary one re-named to the original name. Because of this it is actually quicker to remove fifty "Dead" records than it is to remove one. To save you time Easy base follows the traditional data-base custom of simply marking records for deletion when you delete them. They are only physically removed from the form during a "Pack" operation. When you "Pack" a data file Easy Base also re-writes all the index files for that form. You can pack individual forms with the "Pack Data Forms" utility accessed from the Utilities menu. You can also call the "Pack Data Forms" utility as a User menu function. In a finished application any necessary packing would normally be done from a batch execute menu which would run while the computer was unattended. Forms in which records are regularly deleted should be packed regularly. If packing is not done then not only will you be wasting disk space but search operations will become progressively slower as the percentage of "Dead" records increases. This is fairly obvious - however it is not so obvious that a forms index files can accumulate "Dead" records even though none have been deleted from the main file. An index file is simply the indexed fields contents and record number arranged in order. If, having filed a record you then alter the contents of an indexed field and update the record then the original record in the index file is deleted and a new one entered. Index files with "Dead records" also waste disk space and slow search operations. You should therefore regularly pack any forms which are regularly updated. If you are unsure which forms need to be packed you can use the "Auto-Pack" utility which tests each form and each forms index files for "Dead" records. The form is packed if it or any of its index files is found to have more then 100 total or 10 per cent "Dead" records. Although "Auto-Pack" may seem the easy answer, an application will almost certainly have several forms with "Core" data which remain unchanged and therefore never require packing. If you can identify the forms which do need to be packed and do them individually or from a batch execute menu this will be less time consuming than always using the "Auto-Pack" facility. - 98 - ................................................................................ PAGE NUMBER SYSTEM VALUE PAGE NUMBER Whenever Easy Base is processing a procedure with an output it keeps track of the current page number (Even if you have not included Page Feed commands). You can use the system value "Page Number" to number the pages of your output. To use the "Page Number" value just include "Page Number" in your output field declarations. Declare output fields Customers.name : Customers.ACNO : Page Number end You can then use the "Page Number" field in the Page Header or Page Footer section of your output format. - 99 - ................................................................................ PAUSE ON/OFF PROCEDURE COMMAND PAUSE ON/OFF When an Easy Base procedure has an output to the screen, the output, by default scrolls continuously until the procedure is complete. You can allow the user to pause the output with the "Pause On" and "AutoPause On" commands. If a procedure performs several tasks then the pause facility can be selectively enabled and disabled at any point. Ex. for employees alias types with type in order unique pause off:escape off for employees alias group with type = types.type subindex surname next pause on for employees with subindex in order print list items next next The above code fragment prints employees details in type groups with surname in alphabetic order. During the procedure the user can pause the report while it is listing the output but not while it is subindexing the group. If you set pausing with "Pause On" then the user can pause the output at any point by pressing the return key. If you set pausing with "Autopause On" then the output stops after each screen page of output. If you set "Autopause On" and "Escape On" then the first press of the Escape key cancels the autopausing and the second press terminates the output. "Pause Off" cancels either. If a procedure has pausing set by "Autopause On" and its output is directed to the printer, Easy Base automatically switches to "Pause On" See also: Escape On/Off - 100 - ................................................................................ PI SYSTEM VALUE PI You can use the value of π anywhere in field derivations or procedure code by quoting "Pi". Ex circlearea = Pi / 4 diam^2 - 101 - ................................................................................ PRESELECT PROCEDURE COMMAND PRESELECT The "Preselect" command sets highlighting of the next user menu when the procedure terminates. If a procedure is normally followed by a particular selection from the menu then you can save the user the trouble of choosing it by issuing the "Preselect" command within the procedure. for currentcustomer currentcustomer.acno = input.acno update record next preselect 4 The parameter cannot be supplied as a variable. - 102 - ................................................................................ PRINT PROCEDURE COMMAND PRINT In Easy Base procedures, all output to the screen or printer is initiated with the "Print" command. The "Print" command prints sections from the procedures output format. Ex. '......................procedure code.... Print report header for customers print list items next print report footer '......................Output Format....... .Report Header ════════════════════════════════════════════════ Customer List ════════════════════════════════════════════════ .List Items {customers.name field} {customers.phone field} .Report Footer ════════════════════════════════════════════════ .end The format sections are marked with a full stop in the margin followed by the section name. When you type a full stop in the margin of the "Format editor" Easy Base will offer you a selection of section names. You do not have to use these - they are just some common names that can save you time. To enter your own section names just press escape and type in any name you wish. The ".end" section is not strictly necessary but it is easy to accidentally insert blank lines at the end of your format. By inserting ".end" at the end of the last section you will prevent these from being printed. - 103 - ................................................................................ PRINTER CONTROL PROCEDURE COMMAND PRINTER CONTROL When you send a procedure output to the printer Easy Base will initialize your printer at ten characters per inch, six lines per inch in draft mode. You can alter the printer mode anywhere within a procedure with the following printer controls. Bold On Bold Off Underline On Underline Off (Underlines field contents) Italic On Italic Off NLQ On NLQ Off 8 lpi Sets printing to eight lines per inch 6 lpi Sets printing to six lines per inch 10 cpi Sets printing to ten characters per inch 12 cpi Sets printing to twelve characters per inch 17 cpi sets printing to either 15 or 17 depending on printer Line Feed Advances the print head one line Page Feed Ejects the current page. Landscape Prints in landscape ( HP DeskJet/LaserJet only ) Ex. NLQ On Bold on Print report header Bold Off 12 CPI For customers print list items if bottom margin < 1.5 then Line feed : Bold On : 10 CPI Print Page footer Page feed Print Page header Bold Off : 12 CPI end if next Bold on:Italic On :10 CPI print report footer The above example turns Near Letter Quality printing on for the entire report. It prints page headers and footers in bold at 10 characters per inch, list items at 12 characters per inch and the report footer in bold italics. - 104 - ................................................................................ Prog_Dir System Value Prog_Dir Whenever you run a procedure in Easy Base the drive and directory of the program files is available in the system value Prog_Dir. If you write applications for resale on the Easy Base Runtime module then you will not necessarily know the name of the directory to which the end user has installed your application. If your application opens external files you can ensure that they are in the Application directory by using Prog_Dir. declare variables pathfile as text end pathfile = jointext(Prog_Dir,"\NAMES.TXT") open pathfile - 105 - ................................................................................ PROPER FUNCTION PROPER This function returns the parameter with the first letter of each word in upper case and the rest in lower case. If you wish to enter initials in a field derived as proper and keep them in upper case then there must either be a space or a full stop between them. Ex. Proper(Address) Ex. labelname = upper(company.name) insetname = proper(company.name) Acceptable parameters: Quoted text Text field/variable - 106 - ................................................................................ RANDOM FUNCTION RANDOM This function returns a random number between the upper and lower parameters. Ex. Random(1,100) <Returns a random number between 1 and 100> Ex. The two fields No and card simulate the random cutting of a deck of cards each time the No field is cleared. No field derivation:- if(no <> blank,no,random(1,52)) Card field derivation:- jointext(if(mod(no,13) = 1,"Ace",mod(no,13) = 0,"King", mod(no,13) = 12,"Queen",mod(no,13) = 11,"Jack",mod(no,13)), " of ",if(no < 14,"Hearts",no < 27 ,"Clubs",no <40,"Diamonds", ,"Spades")) Acceptable parameters Numeric Value Numeric expression Numeric field/variable Any other function which returns a numeric value - 107 - ................................................................................ Read File Command Read The "Read" command reads data from a non Easy Base file which has been opened with the "Open" command. The syntax is: Read (Length) to (Field or variable) Length can be absolute, a variable or field or the keyword "Line". Ex. Read 45 to Keyvar Read GetLen to customers.name Read Line to Linestring In the "Line" example Easy Base reads from the current File_pos to the end of the current ASCII line. See also: Open, Close, Seek, Write, Find, Erase - 108 - ................................................................................ REBUILD DIRECTORIES UTILITY REBUILD DIRECTORIES In Easy Base, all DOS file handling is taken care of for you in the background. To do this Easy Base maintains three "Directory" files in which the names which you give to forms, procedures and choice field lists are linked to the actual DOS filenames. The three files are :- BASE.DIR The forms directory PROC.DIR The procedures directory CHOICES.DIR The choice list directory Because these are key files, Easy Base includes a utility which will rebuild them in the event of accidental erasure or corruption. - 109 - ................................................................................ Recall Input Screen Procedure Command Recall Input Screen The Recall Input Screen command, available in network versions only, is used to return to the input screen when an error is detected after a procedure starts. Such errors are invariably caused by multiple work stations running the same procedure at the same time. A typical example would be when a procedure enters a record to a form and one of the fields has to be unique. If two work stations enter the same value for the unique field on their input screens, the error cannot be detected until the procedure is run. The following procedure code illustrates how a procedure can test to see if another workstation has entered the same value in the name field of the parts form. Declare variables x as number end For parts new record For Parts alias check with name = input.name x = x + 1 Next if x > 0 then Beep Display status "This Part Name Has just been taken !" Delay 2000 Recall Input Screen end if copy all from input next The Recall Input Screen command automatically preserves all the field data that had been entered on the input screen irrelevant of the screen's F12 setting for "Clear Screen after Running". - 110 - ................................................................................ RECORD NUMBER SYSTEM VALUE RECORD NUMBER If you derive a field with the formula "record number" it will be filled with the record number at the time of filing or reformatting. Record number differs from "sequence" in the following way. Each time you derive a field with "sequence" you get the next sequencial number irrespective of whether the form has had records deleted and has been repacked. If a file has had at some time 500 records but now only has 20 the next derivation of "sequence" will be 501 and the next derivation of "record number" will be 21. The main use of "record number" is to renumber a set of records after deletions. To do this just add a new field to the form derived as record number. The field will be filled automatically during the data reformatting process. In procedures a records number is available as a pseudo field. Declare Output Fields Customers.Name Customers.Record number end - 111 - ................................................................................ REMARK PROCEDURE COMMAND REMARK You can place remarks anywhere within Easy base procedure code by prefixing them with an apostrophe. Ex. '.................This entire line is a remark..... for pupils with age > 9 if grade = "g" then skip record 'exclude g's from count count = count + 1 'These are remarks next '.............................................. All text to the right of the apostrophe is ignored at run time. - 112 - ................................................................................ Reset Sequence Procedure Command Reset Sequence The reset sequence command (Network Versions only) can be used to reset the sequence number for any given form. The following code resets the sequence number for the customers form to 1000. For Customers Customers.Sequence = 1000 Reset Sequence Exit For Next - 113 - ................................................................................ RESTORE UTILITY RESTORE Easy Base will restore applications or data which it has backed up. You can only restore an application with the "Restore Application" utility called from the utilities menu and you can only restore data-only backups with the "Restore Data" utility called from a user menu. See Also:- Backup - 114 - ................................................................................ RETAIL FUNCTION RETAIL The retail function returns the retail price given the wholesale price and the discount as a percentage of retail. Ex Retail(cost,discount) < returns 125 if cost = 100 and discount = 20> - 115 - ................................................................................ RETEST DERIVATION PREFIX RETEST In Easy Base, fields are derived when a new record is started and when any other field which affects the derivation is changed. You can force a field to be rederived when F2 is pressed by prefixing the derivation with "retest". Suppose you have a field "degrees" in which the user must enter a number between 1 and 360. To prevent him entering an out of range number you could derive the field :- If(degrees<1 or degrees > 360,blank[beepInvalid number of degreesCursor degrees],degrees) The above derivation would prevent the user from filing a record with an out of range number. However, by the time he read the message, his entry would be blanked out. It would be better if the number he had typed could be left in the field so that he could see his error. To do this all you have to do is change "blank" to "degrees" in the above derivation. This however would allow the user ( Should he decide to ignore the message ) to then file the incorrect value. To leave the value in the field and still prevent the user from filing it, use the "retest" prefix. Retest If(degrees<1 or degrees > 360,degrees[beepInvalid number of degreesCursor degrees],degrees) Note:- 1. You can have more than one prefix on any one field derivation provided there is a space between each. - 116 - ................................................................................ REVERSE FUNCTION REVERSE The reverse function is used in the creation of compound index fields. What it actually does is alter the characters of the parameter in such a way that an alphabetic listing of a field after reversal will have the reverse order to that of the original characters before using the reverse function. In the example shown for the function "zeropad", had you wished to list the pupils in descending age order there would be no point in using the procedure code: for pupils with unifield in reverse order as this would not only reverse the age grouping but would also reverse the name order. To produce the desired printout use exactly the same procedure but derive "unifield" as:- Jointext(reverse(zeropad(age,2,0)),name) - 117 - ................................................................................ RIGHTTEXT FUNCTION RIGHTTEXT This function returns a specified number of characters from the end of a field or variable. Ex Righttext(name,10) Ex. Righttext(datetext(system date),4) < returns 1993 , 1994 Etc.> Acceptable parameters: Text field/variable Any function returning a text value - 118 - ................................................................................ ROUND FUNCTION ROUND There are three rounding functions in Easy base:- Round Roundup Rounddown If a rounding function is called with a single parameter then the return value is the parameter rounded to the nearest integer value. Ex. Round(items/reps) < if items = 9 and reps = 4. 2 is returned > All rounding functions accept a second parameter indicating the number of decimal places (or powers of 10 if negative) to round to. Ex. Rounddown(vaton(net),2) < Rounds down the VAT on field "net" to the nearest penny. Ex. Jointext(roundup(if(mod(years,100) = 0,years+1,years),-2 )/100,"th Century") < Returns "20th Century" for all values of years 1900-1999 > Round Produces a default rounding system in which:- 2.4 rounds to 2 2.5 rounds to 3 -2.4 rounds to -2 -2.5 rounds to -3 In the Roundup and Rounddown functions the direction is sign sensitive:- 2.5 roundsup to 3 2.5 roundsdown to 2 -2.5 roundsup to -2 -2.5 roundsdown to -3 Acceptable parameters: Numeric value Numeric expression Numeric field/variable Any other function which returns a numeric value - 119 - ................................................................................ RUN FIELD CONTROL RUN The "run" control is used to initiate procedures with input screens without the user having to press F2. Ex. If(lookup(customers,name) = blank,blank,lookup(customers, name)[run]) In the above example, a procedure which lists customers accounts has an input screen with a single field where the customers name is entered. When the user enters a name the above derivation will test whether or not the entered name is a customer. If the entered name does not exist in the customers file the field will be blanked out but if it does then the procedure will run without the user having to press F2. - 120 - ................................................................................ RUN PROCEDURE COMMAND RUN The "Run" command terminates the current procedure and starts another. The "Run" command can be used to chain procedures similar to placing them on a batch execute menu. Its main use, however, is to branch to other procedures depending on which function key was pressed from the input screen. Procedures can be initiated from their input screens by pressing any of the function keys F2, F4, F5, F7, F8, F9 or F10. The number of the function key which was pressed is returned in the system value "Fun_Key". If the procedure was initiated by the field control [Run] then the Fun_Key value returns 0. Example. If Fun_Key = 7 then Run Import1 If Fun_Key = 8 then Run Import2 If Fun_key = 2 then for customers with name = input.name print list items next end if If you press F1 after typing "Run" in the procedure code editor, Easy Base will list all your procedure names for you to pick from and ensure correct spelling. The Run command only transfers control to the next procedure when the main procedure is executed from a user menu. When you are developing your procedures in the procedure generator the called procedure cannot be loaded. If the code for a procedure being tested in the procedure generator reaches a "Run" command, the test ends with the message "Run call made to..OK" to let you know that the call will be made when the procedure is run live from a user menu. See also: Fun_Key Disable - 121 - ................................................................................ SCREEN COLOURS UTILITY SCREEN COLOURS The "Screen Colours" utility provides 14 pre defined colour sets including two for "Mono Graphics" cards together with a user defined setup. To change the colours of your system simply select "Screen Colours" from the Utilities menu and select the set you want. The "Screen Colours" utility can also be called as a User Menu function allowing an end user to change his screen set from your menus without accessing the system. - 122 - ................................................................................ SECONDS FUNCTION SECONDS This function returns the seconds number from the time parameter. Ex. seconds(system time) Acceptable parameters: System time Time field Maketime function Time expression - 123 - ................................................................................ Seek File Command Seek The "Seek" command moves the Read/Write pointer within a non Easy Base file which has been opened with the "Open" Command. The actual position of the pointer at any time is available in the system value File_Pos. The File_Pos value is the pointers current offset in bytes (Numbering the first byte as 1). The Seek Command accepts an actual offset, an offset relative to File_Pos or File_Len or an ASCII line movement. Examples. Seek 245 Seek File_Pos + 35 Seek File_Len - 20 Seek Line_Forward Seek Line_Back You cannot combine an ASCII line movement with an offset - To read ten bytes starting at byte 30 on the next line you need two Seek commands. Seek Line_Forward Seek File_Pos + 29 read 10 to Parts.No The line movement Line_Forward always positions the pointer at the start of the next line. The line movement Line_Back moves the pointer to the start of the current line if it is not already there and to the start of the previous line if it is. Some files (Eg. Email) have a non ASCII header followed by ASCII lines for the actual message. You can use Seek Line_Forward within a Do Loop to skip though the non ASCII part. If Line_Forward does not find a Carriage Return and Line Feed sequence within 255 bytes then it leaves the file pointer 255 bytes ahead of its last position. There is no corresponding facility in Line_Back. If Line_Back does not find a line feed (or Start of File) within 255 bytes then the file pointer does not move. See also: Open, Close, Read, Write, Find, Erase - 124 - ................................................................................ SEQUENCE SYSTEM VALUE SEQUENCE When you create a new form a sequence value is updated each time a record is entered. The "sequence" value starts at 1 and continues to 2 billion irrespective of whether or not records have been deleted from the form. If you derive a field as "sequence" then each new record will have the next sequenced number. Sequence derivations are useful for such things as invoice numbers. A sequence field need not start at one however. Ex. sequence + 1000 would number the first record 1001 The sequence number for any form can be reset in procedure code. For Form Form.Sequence = 1 Reset Sequence exit for next - 125 - ................................................................................ SET PAPER LENGTH UTILITY SET PAPER LENGTH The "Set Paper Length" utility is accessed from the Utilities menu and can also be called from a user menu. There are settings for A3, A4, A5, American Letter and American Legal in both portrait and landscape. When you change the paper length in use then you update pagination for all report outputs. The landscape options are for use in wide carriage printers where the paper can be fed in landscape orientation they do not produce "sideways" printing on narrow carriage printers. If the paper size you use is not one of those listed, select "Other" and you can enter the length and width of your paper manually. The Length is entered in "Lines" ( One Sixths of an inch ) and the width is entered in inches. For example, if your paper is 12 inches by 8.25 inches you would enter the length as 72 and the width as 8.25. - 126 - ................................................................................ Shell File Command Shell The "Shell" command executes a DOS command which you supply as the parameter. Shell "DIR C:\WP > C:\EB\DIR.TXT" This writes a directory listing of C:\WP to the file DIR.TXT which you can then open and read from. You can supply the parameter as quoted text, as a variable or field or with a function. Shell Jointext("copy C:\PROGS\",input.directory,"\*.* A:\") In this example a disk vendor can select programs by name on an input screen which looks up the directory in which the program is stored. The procedure then copies all files from that directory to the A: drive. Depending on the procedure you are running, Easy Base uses between 450K and 550K of base memory. You cannot therefore "Shell" to any major program. The main use of "Shell" is to use the DOS copy and Dir commands. You can also shell to a batch file of such commands if you have several tasks to perform. CAUTION The Easy Base Executable file expects to find its support files in the current default directory. If you change drive or directory while shelled out of Easy Base then you must change back before exiting the procedure. If you are writing an application for resale then remember that the end user might not have installed it to C:\EB. Change back to the correct drive and directory by using the system value "Prog_Dir". Declare variables Drive as text : Dir as text end drive = lefttext(prog_dir,2) dir = righttext(prog_dir,lengthtext(prog_dir)-3) shell "Job.Bat" shell drive shell jointext("CD\",dir) When you use "Shell" Easy Base clears the screen and pauses for any DOS error messages. If you know that the command you are about to shell to will not invoke a response from DOS you can add the keyword "Smooth" and Easy Base will not clear the screen or pause. Shell Smooth "DIR C:\FAX > C:\EB\DIR.TXT" - 127 - ................................................................................ Show PickList PROCEDURE COMMAND Show PickList In most instances where you select records for displaying, printing or further processing you will be able to select based on a group field or a range of field values. There will however be occasions where you need to select a set of records which are totally unrelated. To do this Easy Base includes a pre defined system form called "PickList". This form has two fields called "Item" and "Mark". The PickList form is only accessible within procedures. You can process it just like one of your own forms (Except that you cannot "Create Temp as PickList") and you can display it at any point in a procedure with the command "Show PickList". The difference between PickList and any other form is that it displays as a table. The Item field is no entry and the Mark field is a choice field which is either Blank or ASCII 251 ( √ ). After displaying the form you can continue your processing based on the value of the "Mark" field. As an example of the use of the picklist form, suppose that you had a contacts form with names and addresses. There is also a group field which is either Customer, Supplier, or Employee You want to send a circular letter to your Customers - but not all of them - only the ones you know personally. There is no sub grouping for this - you need to actually "Tick off" the ones you require from a list. .......................................... Declare output fields Contacts.Name : Contacts.address : date End For Contacts with Group = "Customer" For PickList new record Picklist.Item = Contacts.Name Next Next Show PickList For Picklist with Mark = Chr$(251) For Contacts with name = Picklist.Item Print Letter Next Next Clear Records from PickList ............................................ At the line "Show Picklist" Easy Base will display all your customers names in a table which you can scroll through marking or unmarking the customers to which you wish to print the circular. When you Escape from the PickList form, processing continues in the procedure code, printing the Letter only to those customers which you have "Ticked" ie with the "Mark" field = Chr$(251). PickList is not a temporary form. You can add or delete records in one procedure and show it in another. You must clear records from one usage before the next. Cont. - 128 - ................................................................................ Show PickList PROCEDURE COMMAND Show PickList The Item field in Picklist is a text field 80 characters in length. You can show from 1 to 65 characters of this field by stating the length after the command "Show PickList 20" If you do not enter a length the default is 35. There is no problem allocating numeric values to PickList.Item but you cannot show a Time field. You cannot show a date field directly but you can use Picklist for dates if you have a field with the date shown in text derived with the datetext function. For Events with Type = "Committee Meeting" For PickList new record Picklist.Item = Events.Dateastext PickList.Mark = Chr$(251) next Next Show PickList 18 In the above example you will see that the "Mark" field is also pre filled. In this case, when the list displays, all the dates will be pre marked. The following keys are active when PickList is displayed. Up Down PgUp PgDn Home End Space Marks or unmarks individual items F4 Marks or unmarks all items F5 Swaps marked for unmarked F10 Makes a shortlist of marked items only F2 Returns to the procedure code. - 129 - ................................................................................ SKIP GROUP PROCEDURE COMMAND SKIP GROUP The "Skip Group" command excludes an entire group of records from selection by a "For" loop. Ex. for employees with type in order if employees.type = "Part time" then skip group print list items next The "Skip Group" command is not the same as filtering with a "<>" condition. You could achieve the same result with :- for employees with type in order if employees.type <> "Part time" then print list items end if next but the second procedure would take much longer to run as each part time employee's record would be loaded into memory only to be rejected by the "If" condition. The first procedure on the other hand only loads a single part time employee's record then skips the entire group by advancing the index pointer in use for record selection. You can also use the "Skip Group" command to terminate selection of a group of records at a given point. declare variables count as number end for employees with type in order count = count + 1 if count = 51 then count = 0 skip group end if print list items next The above procedure prints the first fifty employees in each "type" group. The "Skip Group" command can be shortened to "Skipgrp" IMPORTANT: The condition for the Skip Group cannot be nested within another "If" condition or "DO LOOP" structure which is itself within the current "For" loop. :- See Skip Record for example. - 130 - ................................................................................ SKIP RECORD PROCEDURE COMMAND SKIP RECORD The "Skip Record" command is used to exclude individual records from selection in a "For" Loop. Ex. for employee with worksno in order if employees.worksno = 12 then skip record print list items next The "Skip Record" command has exactly the same effect as filtering with a "<>" condition but is much easier to write if there are many items to exclude for employees with worksno in order if employees.worksno = 12 or employees.worksno = 15 then skip record end if if employees.name = "smith" then skip record if employees.age > 50 then skip record next You can exclude groups of records from an inner loop by applying the "Skip Record" command to a "Unique" outer loop. for employees alias types with type in order unique if types.type = "director" then skip record if types.type = "part time" then skip record for employees with type = types.type print list items next next If you need to exclude groups of records from a single loop you must use the "Skip Group" command. The "Skip Record" command can be shortened to "Skiprec". IMPORTANT: The Skip Record command causes a direct jump to the start of the next iteration of the current "For" loop. The condition for the Skip Record must not be nested within another "If" condition or "Do Loop" structure which is itself within the current For loop. for form if (condition) then if form.type = "Part time" then skip record end if next This will cause a system error. - 131 - ................................................................................ SPACEPAD FUNCTION SPACEPAD The Spacepad function pads a text variable to a given length with spaces. Ex. Spacepad(name,10) < returns "Fred " if name = "Fred" The spacepad function is used in the creation of compound index fields. For example: A "Films" form has the fields "title" and "category". To list the films grouped by category in order and with the titles in each category in order create the field "groupindex" as text,long enough to hold both category and title fields and derived as :- Jointext(spacepad(category,15),title) {15 is length of category field} You can then produce the required printout with the following procedure. ....................format......................... .Report header ═══════════════════════════════════════════════════════════ FILM LIST BY CATEGORY ═══════════════════════════════════════════════════════════ .Group header Films in {category field} .List items {title field} Starring {Star field} Running time {length Fld} .end ......................procedure code................. declare output fields films.title:films.star:films.length:films.category end declare variables catcheck as text end '............ bold on print report header for films with groupindex in order if films.category <> catcheck then bold on print group header bold off end if print list items next - 132 - ................................................................................ SPELLDAY FUNCTION SPELLDAY This function spells out the day of the number (1-7) parameter. Ex. Spellday(dayofweek(system date)) Acceptable parameters: Numeric value (1-7) Numeric expression evaluating to (1-7) Numeric Field/variable holding value (1-7) Any function returning a numeric value (1-7) - 133 - ................................................................................ SPELLMONTH FUNCTION SPELLMONTH This function spells out the day of the number (1-12) parameter. Ex. Spellmonth(month(system date)) Ex. Month = Spellmonth(month(invoices.date)) Acceptable parameters: Numeric value (1-12) Numeric expression evaluating to (1-12) Numeric Field/variable holding value (1-12) Any function returning a numeric value (1-12) - 134 - ................................................................................ START HERE DERIVATION PREFIX START HERE If, in a form or procedure input screen, you want the cursor to start in other than the top left field then derive the field in which you wish the cursor to start with "Start here" . If the field in which the cursor is to start already has a derivation then simply prefix the derivation with "Start here". Ex. start here Lookup(customers,name) 1. You can have more than one prefix on any one field derivation provided there is a space between each. - 135 - ................................................................................ STRINGOF FUNCTION STRINGOF This function returns a string of characters. Ex. Stringof(12,"-") < Returns "------------"> Ex. Stringof(5,chr$(240)) < Returns "≡≡≡≡≡"> One of the most useful applications of the "Stringof" function is to pad output fields in tabular reports with "." characters. Ex. Declare output fields stock.name : stock.price end for stock with name in order stock.name = jointext(stock.name,stringof(30-lengthtext( stock.name),".") print list items next The printed output from the above report would be in the form:- Gizmo......................... 2.50 Sprocket...................... 9.95 Widget........................ 3.10 Acceptable parameters: 1 Any numeric field, variable, expression or function 2 Single quoted character - Function returning a single character - 136 - ................................................................................ SUBINDEX PROCEDURE COMMAND SUBINDEX The command Subindex is used to create an index file which contains only a subset of the records in a form. Subset indices are used in reports to output records in the order of one field when they have been selected using the index file on another field. For example, if you wish to list all the employees who work at the "Manchester" branch with their surnames in order you need to select the records using the index on the "Branch" field but you need to list them using an index on the "Surname" field. Since you can only use one index at a time you need to create an index on "Surname" which only contains the records which have "Manchester" as the branch. For Employees with branch = "Manchester" Subindex surname as #1 next for Employees with subindex 1 in order print list items next You can have up to nine subset indices in any one procedure and you can select subsets from subsets. For example, if, in the employees list you only wished to list female employees whose salary was greater than 50,000 you would use:- For Employees with branch = "Manchester" Subindex sex as #1 next For Employees with subindex 1 = "Female" Subindex salary as #2 next For Employees with subindex 2 > 50000 Subindex surname as #3 next for Employees with subindex 3 in order Print List Items next Subset indices are temporary files. You cannot use a subset index in one procedure that was created in another. Within one procedure, however, you can use a subset index almost as if it were a form. You can add records to it as often as you wish before using it and you can erase its contents with the "Clear records from" command. Clear records from subindex 1 This allows you to build subsets with multiple selections eg. Employees with branch = "Manchester" or branch = "Birmingham" and to reuse a subset index in a nested loop eg. Employees grouped by branch in surname order. - 137 - ................................................................................ SUBINDEX PROCEDURE COMMAND SUBINDEX Example of multiple selections: For Employees with branch = "Manchester" Subindex surname as #1 next For Employees with branch = "Birmingham" Subindex surname as #1 next For Employees with subindex 1 in order Print List Items next Example of nested subset: For Branches with name in order Print group header For Employees with branch = branches.name Subindex surname as #1 next For Employees with subindex 1 in order Print List Items next Print group footer Clear records from subindex 1 next In the first example, the records with branch = "Birmingham" are added to those with branch = "Manchester" and both are listed as a single subset. In the second example each individual branch subset is printed and then erased ready for the next. Whenever Easy Base is writing to a subset index the "Running" flash on the upper status line is replaced with "Sorting". - 138 - ................................................................................ SYSTEM DATE SYSTEM VALUE SYSTEM DATE You can use your computers inbuilt date anywhere in field derivations or procedure code by simply quoting "system date". Ex. for diary with date = system date print list items next - 139 - ................................................................................ SYSTEM TIME SYSTEM VALUE SYSTEM TIME You can use your computers inbuilt time anywhere in field derivations or procedure code by simply quoting "system time". Ex. for stockitems with stockno = input.stockno lastcheckeddate = system date lastcheckedtime = system time update record next - 140 - ................................................................................ TEXT FIELD TYPE TEXT The text field type can be up to 80 characters long. Cut copy and paste operations are available between all text, and textblock fields : Use Shift plus the arrow keys to mark the text you wish to cut or copy. When you release the keys the cut or copy choice will appear. To paste cut text position the cursor where you want it inserted and press Shift plus Ins. Spell Checking is also available in text fields - Press Ctrl + S - 141 - ................................................................................ TEXT BLOCK FIELD TYPE TEXT BLOCK When you Define a field with the field type "Text Block" then instead of entering a field length you shade out the area which you wish the field to cover using the arrow keys. Textblock fields offer many of the facilities of a wordprocessor including cut and paste, wordwrap and spell checking. Useful widths for A4 and Letter size paper are 63 characters if you intend to print at 10 characters per inch and 75 characters if you intend to print at 12 characters per inch. This document for instance was created in 63 character wide text blocks. The maximum width of a text block field on screen is 80 characters. You can however redefine it up to 230 characters wide in procedure printouts and Easy Base will reformat your paragraphs to the new width. A text block field is not a "memo" field. It can be indexed and derived. - 142 - ................................................................................ TIME FIELD TYPE TIME The Easy Base "time" field has the eight character format:- 11:20:20 (Hours:Minutes:Seconds) You can record time data in any format you wish by using a "Formatted Text" field but you must use a "Time" field if you wish to perform addition and subtraction operations on time values. (in seconds) Time fields are automatically checked for validity. See Also:- Maketime - 143 - ................................................................................ TIMEAMPM FUNCTION TIMEAMPM This function returns the hour ( 0 - 12) plus am. or pm. from the time field parameter. Ex. Timeampm(system time) Ex. -------------------------format---------------------------- .Report header Appointments for {Apps.Date Field} .List items {Apps.Name Field } at {Time Field} .End -------------------------code---------------------------- declare output fields Apps.name Apps.date Time end print report header for apps with date = system date time = timeampm(apps.time) print list items next Acceptable parameters: System time Time field Maketime function Time expression - 144 - ................................................................................ TOTAL COPIES SYSTEM VALUE TOTAL COPIES Whenever a procedure enters a "FOR" loop qualified by "With fieldname = " the total number of records which match the qualification value is available in the system value "Total Copies" Total Copies is extracted directly from the index file and relieves you of a time consuming loop all the way through a form updating a counter. In other words:- for books with category = "fiction" count = count + 1 next can be replaced with:- for books with category = "fiction" count = total copies exit for next Unlike "Total Records" the "Total Copies" value never includes records marked for deletion. NETWORK VERSIONS With the release of Network V1 the Total Copies system value has been replaced with a pseudo field Form.Total Copies. - 145 - ................................................................................ TOTAL RECORDS SYSTEM VALUE TOTAL RECORDS Whenever a procedure enters a "FOR" loop the total number of records in the form is available in the system value "Total records" Ex. The following code fragment updates a "Customer summary" form with the total number of customers in the "Customers" form. declare variables tot as number end for customers with acno = 1 tot = total records exit for next for customersummary customersummary.totalcustomers = tot update record next Caution. Provided that the "For" loop is qualified by a "with" statement then the "Total Records" value is extracted directly from the open index file and is the total number of "Live Records". If you access the "Total records" value in an unqualified "For" loop then there is no index file open and the "Total Records" value is derived by dividing the forms file length by the record length. It therefore represents the total number of records in the form "Live" and "Deleted". NETWORK VERSIONS From the release of Network V1 the Total records value has been replaced by a pseudo field Form.Total Records. for customers with acno = 1 for customersummary customersummary.totalcustomers = customers.total records next exit for next - 146 - ................................................................................ UPDATE RECORD PROCEDURE COMMAND UPDATE RECORD The "Update record" command is used to alter the contents of one or many records in a form. Ex. for stock with item = input.item stock.price = input.price stock.pricechangedate = system date update record next This example changes the price of a single "Stock" item. for employees with taxcode = input.oldtaxcode employees.taxcode = input.newtaxcode update record next This example changes the taxcode field of all the employees whose present taxcode is the same as the input screen "oldtaxcode" to the input screen "newtaxcode". for employees employees.taxcode = employees.taxcode + 60 update record next This example increases the taxcode of all employees by sixty. - 147 - ................................................................................ UPPER FUNCTION UPPER Returns the Uppercase of a text field or variable. Ex. Upper(postcode) < shows the postcode field in uppercase> Ex. '....................... declare output fields labelname labeladdress labelcode end for customers with maillist = "yes" Labelname = Upper(customers.name) Labeladdress = Upper(customers.address) Labelcode = customers.postcode print labels next Acceptable parameters: Quoted text Text field/variable - 148 - ................................................................................ V.A.T. FUNCTIONS FUNCTION V.A.T. FUNCTIONS Easy Base has a set of five VAT rates which can be altered from the utilities menu. There are four VAT functions:- VATin VATon PlusVAT MinusVAT Ex. VATin(retailprice,2) < Returns the VAT content of "retailprice" at VAT rate 2 Ex. VATon(netprice,4) < Returns the VAT to be added to "netprice" at VAT rate 4 Ex. PlusVAT(netprice,3) < Returns the VAT inclusive price at VAT rate 3> Ex. MinusVAT(retailprice,2) < Returns the net price of "retailprice" before VAT at VAT rate 2> (assumes Retailprice as VAT Inclusive and Netprice exclusive) If the second parameter (rate) is omitted from any of the VAT functions then Rate 1 is assumed. Ex. Gross = PlusVAT(input.net) < The variable "Gross" = the VAT inclusive of the input screen field "net" calculated at rate 1.> Although the VAT functions are primarily included for business applications, they can be used for any purpose where a percentage needs to be changed globally throughout an application without the need to alter each procedure or field derivation in which it is used. Acceptable parameters: 1 Any numeric value, field, variable, expression or function 2 As Parameter 1 but evaluating between 1 an 5 - 149 - ................................................................................ Write File Command Write The "Write" command writes data to a non Easy Base file which has been opened with the "Open" command. The Syntax is: Write (Text, Variable, Field or Function) [Line_len (length)] The Line_Len keyword and parameter are not required. Ex. Write "Once upon a time" Write LineVar Write Customers.name Write Jointext(customers.forname," ",customers.surname) Write Customers.notes line_len 60 Notes:- The contents of a text field is supplied without leading or trailing spaces. The contents of a text variable is supplied without trailing spaces. You can pad the output with either of the functions "Spacepad" or "stringof". The following code writes customers names and account Nos in two columns to an ASCII file. Open "C:\WP\NAMES.TXT" for customers with name in order write stringof(5,chr$(32)) write spacepad(customers.name,30) write customers.acno write chr$(13) write chr$(10) next The contents of a text block field is supplied reformatable ie a single CHR$(13) is used to denote where a new line is to be forced. If you write a text block field without the Line_len keyword it will be written in this format. To write a text block field as ASCII Lines use the Line_Len keyword. The Line_Len value does not have to be the same as the width of the text block field. Easy Base will word wrap the contents to whatever Lin_Len value you supply. You cannot write quotation marks within quoted text. To write "Fred said "@*!" to Bill" you must replace the inner quotation marks with ASCII character 127. (Hold down the Alt key and type 127 on the numeric keypad) See also: Open, Close, Read, Seek, Find - 150 - ................................................................................ YEAR FUNCTION YEAR This function returns the year number from a date parameter. Ex. Year(system date) Ex. Carage = jointext((year(system date)-year(registered))," Years old") Acceptable parameters: System date Date field Makedate function Date expression - 151 - ................................................................................ ZEROPAD FUNCTION ZEROPAD The Zeropad function pads an integer or fixed point field to a given number of places left and right of the decimal point. Ex. Zeropad(price,3,2) <Returns "001.50" if price = 1.5> Ex. Zeropad(acno,6,0) <Returns "004532" if acno = 4,532> The Zeropad function can be used as in the second example to return a fixed length numeric string from an integer field. It's main use, however, is in the creation of fields for compound indices where one of the fields to be compounded is a number. For example: A school has all pupils names and ages recorded on a form in fields "name" and "age". To create an index which will list pupils names alphabetically but grouped by age a compound field which is invisible and does not allow user entry is created as a text field derived with:- Jointext(zeropad(age,2,0),name) Because the compound field is text the index file will be sorted alphabetically. If you sort the numbers 8,9 and 12 alphabetically you get 12,8,9 because the one in 12 comes before 8. If however you sort the numbers 08,09 and 12 alphabetically you get the correct numeric order. By using the zeropad function in the above example and indexing the compound field,let's call it "unifield" you could then print out the pupils in age groups with the names in each group arranged alphabetically. .........................format................. .group header Pupils aged {pupils.age field} .list items {pupils.name field} .end .........................procedure code......... declare output fields pupils.name:pupils.age end declare variables agecheck as number end agecheck = 0 for pupils with unifield in order if pupils.age <> agecheck then print group header agecheck = pupils.age print list items next - 152 - ................................................................................